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
"davegb" wrote:

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?