VBA Excel Code
Hi...
We have many speadsheets within our workplace that alot of people have access to.
These spreadsheets have many sheets (tabs) which all have auto filters set.
The problems we have are people are leaving the auto filters on and then when somebody else goes in the spreadsheet they are reporting wrong information because they havent relaised the filter is still on.
I have in place a macro that removes all filters upon opening the spreadsheet put this currently only works on the first (active) sheet.
The code i have at the moment is below:
'put the autofilter back on if it has been removed.
If ActiveSheet.AutoFilterMode
'error handle the showalldata part
On Error Resume Next
For Each ws In ActiveWorkbook.ShowAllData
On Error GoTo 0
Is there something i can change for it to apply to all sheets.
Thank You.
We have many speadsheets within our workplace that alot of people have access to.
These spreadsheets have many sheets (tabs) which all have auto filters set.
The problems we have are people are leaving the auto filters on and then when somebody else goes in the spreadsheet they are reporting wrong information because they havent relaised the filter is still on.
I have in place a macro that removes all filters upon opening the spreadsheet put this currently only works on the first (active) sheet.
The code i have at the moment is below:
'put the autofilter back on if it has been removed.
If ActiveSheet.AutoFilterMode
'error handle the showalldata part
On Error Resume Next
For Each ws In ActiveWorkbook.ShowAllData
On Error GoTo 0
Is there something i can change for it to apply to all sheets.
Thank You.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
when i use the code given it give me a Microsoft Visual Basic Error
Run-Time Error 1004
The command could not be completed by using the range specified. Select a single cell within the range and try the command again.
Behind the error it does work the filters have been reset.
Run-Time Error 1004
The command could not be completed by using the range specified. Select a single cell within the range and try the command again.
Behind the error it does work the filters have been reset.
Do you have header values in row 1 of each worksheet?
Kevin
Kevin
ASKER
yes i'm testing this on a test spreadsheet at the moment so A1 and B1 have "name" and "code" in.
Then you shouldn't get any errors. Are there ANY worksheets in the workbook that do not have headers and which you do not want to be filtered?
Kevin
Kevin
ASKER
Excellent it worked, because it was just a test spreadsheet i had sheet 1 sheet 2 and sheet 3.
sheet 3 wasnt populated....
Just deleted and No errors...
Thank you very much
Daniel
sheet 3 wasnt populated....
Just deleted and No errors...
Thank you very much
Daniel
ASKER
very quick
For Each ws In ThisWorkbook.Worksheets
ws.Rows(1).AutoFilter
Next ws
Kevin