Why doesn't this work?

Sub showall()
'
Dim sh As Worksheet
'
Application.Calculation = xlManual
For Each sh In ThisWorkbook.Worksheets
sh.Activate
ActiveSheet.ShowAllData
Next sh
Application.Calculation = xlAutomatic
End Sub

I recorded a macro on a single sheet to show all data and it returned
"activesheet.showalldata". What I'm trying to do is to scroll through every
worksheet in a workbook and show all if there is a filter on the page.

If there is no filter on the worksheet, then it'll fail.

If a worksheet is protected, then it'll fail.

Sub showall()
'
Dim sh As Worksheet
'
Application.Calculation = xlManual
For Each sh In ThisWorkbook.Worksheets
'no need to select
If sh.FilterMode Then
sh.ShowAllData
End If
Application.Calculation = xlAutomatic
End Sub

Steve wrote:

Why doesn't this work?

Sub showall()
'
Dim sh As Worksheet
'
Application.Calculation = xlManual
For Each sh In ThisWorkbook.Worksheets
sh.Activate
ActiveSheet.ShowAllData
Next sh
Application.Calculation = xlAutomatic
End Sub

I recorded a macro on a single sheet to show all data and it returned
"activesheet.showalldata". What I'm trying to do is to scroll through every
worksheet in a workbook and show all if there is a filter on the page.

--

Dave Peterson

I found out the problem. If there's not an autofilter on a given sheet, the
macro bombs. Is there a way to tell whether an autofilter exists? I want to
only show all data...not remove the autofilter.

Thank you.

"Dave Peterson" wrote:

If there is no filter on the worksheet, then it'll fail.

If a worksheet is protected, then it'll fail.

Sub showall()
'
Dim sh As Worksheet
'
Application.Calculation = xlManual
For Each sh In ThisWorkbook.Worksheets
'no need to select
If sh.FilterMode Then
sh.ShowAllData
End If
Application.Calculation = xlAutomatic
End Sub

Steve wrote:

Why doesn't this work?

Sub showall()
'
Dim sh As Worksheet
'
Application.Calculation = xlManual
For Each sh In ThisWorkbook.Worksheets
sh.Activate
ActiveSheet.ShowAllData
Next sh
Application.Calculation = xlAutomatic
End Sub

I recorded a macro on a single sheet to show all data and it returned
"activesheet.showalldata". What I'm trying to do is to scroll through every
worksheet in a workbook and show all if there is a filter on the page.

--

Dave Peterson