Autmatically remove filters from all worksheets

[davegb]

I'm creating spreadsheets that contain buttons/macros to filter for
specific criteria. I want all records/rows to be shown when the
workbook is opened. I have 2 macros to accomplish this. One is attached
to a button which appears on every worksheet to remove any filters.

Sub ShowAll()
'
' ShowAll Macro
' Macro recorded 3/11/2005 by Dave Bellamy

Range("a1:J62").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("S5:Z6"), Unique:=False
Range("c4").Select

End Sub

I used blank filter criteria instead of other methods that I found to
remove filters because none of the others worked in a protected sheet.

The second macro goes through all sheets in the workbook and removes
all filters automatically on opening. Or it should. It calls the first
program to do so. I've stepped through the macro, and it goes to both
sheets (I'm testing with 2 sheets, more will come later so I want the
macro to run on however many sheets there are in the workbook). But it
doesn't remove the filter in the second sheet.

Sub Auto_Open()
Dim Wks As Object
For Each Wks In ThisWorkbook.Worksheets
On Error Resume Next
ShowAll
Next Wks
End Sub

Does anyone know what's wrong?

 

[Guest]

davegb,

When you start switching between more than one sheet you need to qualify to
excel which sheet you are refering to

try
Sub ShowAll()
'
' ShowAll Macro
' Macro recorded 3/11/2005 by Dave Bellamy

activesheet. Range("a1:J62").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
activesheet.Range("S5:Z6"), Unique:=False
activesheet.Range("c4").Select

End Sub

Sub Auto_Open()
Dim Wks As Object
For Each Wks In ThisWorkbook.Worksheets
On Error Resume Next
wks.activate
ShowAll
Next Wks
End Sub


ben

 

[Nigel]

Hi, Try the following

Sub Auto_Open()
For Each Wks In ThisWorkbook.Worksheets
On Error Resume Next
Wks.ShowAllData
Next Wks
End Sub

 

[Guest]

Pass in the worksheet object to the showall procedure something like this

Sub ShowAll(byval wks as worksheet)
'
' ShowAll Macro
' Macro recorded 3/11/2005 by Dave Bellamy

wks.Range("a1:J62").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
wks.Range("S5:Z6"), Unique:=False
wks.Range("c4").Select

End Sub

That is untested but I think it will work...

HTH

 

[davegb]

Tried it, Nigel, does the same thing mine does. Removes filters on the
first sheet, but not the other.
Thanks for trying!

 

[davegb]

Thanks Jim, but it didn't work either!

 

[davegb]

Ben remove this if mailing direct,
Bingo! This one worked! You win the prize!

 

[Guest]

yeah let's not go into just how much of a pain in the butt that was trying to
figure out when i first started changing sheets on my first project.

 

[davegb]

I can imagine! Had the same experience with learning VBA several times.
Thanks for passing it along!