Hi everyone, first time posting and also first time getting involved with Excel's VBA and Macros so I'm a little out of my depth!
My aim for my workbook is as follows:- When a Quote has been sent, someone will add the date in the 'Done' column.
- This will then automatically get sent to a different sheet, so that all of the customers with the quotes sent are in one place.
- The customers for which the Quote has been sent will then be
- filtered out, so that it makes it easier to see what is still to do.
To achieve this, I have:- Set up a macro to, using advanced filtering, check for content in the 'Done' column. If there is something there, it will copy that to a different sheet.
- Set up a macro to automatically refresh the workbook whenever anything is typed, so that the book is constantly sending finished quotes to the other sheet.
- Set up a view with a filter that hides a customer UNLESS their 'Done' column is empty.
Because of the way advanced filtering works, the view has to be toggle-able so that additional information can still be entered into the sheet after a quote has been done. I initially had the auto-refresh macro also apply the filter to hide 'Done' quotes, however realised this made it impossible to change any of these entries.
My problem is that every time the workbook refreshes, it turns off the filter for hiding 'Done' quotes. This means the view is useless and doesn't actually differ from the Default view.
I am making this workbook for my colleagues who are less adept in Excel than me, so having them manually reapply the filter each time is not ideal and so I am trying to find another solution.
I have made a macro to apply the filter, but ideally this would happen automatically, however ONLY when using a specific view.
Is there a way for VBA to first check the view before applying the filter?
My macros are as follows:
Sub Update_Spreadsheet()
'
' Update_Spreadsheet Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Sheets("To Do").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Macro Rules").Range("A2:A3"), CopyToRange:=Range( _
"Table13[#All]"), Unique:=False
Sheets("To Do").ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:="="
End Sub
Sub Fix_View()
'
' Fix_View Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Selection.AutoFilter
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:="="
End Sub
Bookmarks