showall not working

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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
 
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 said:
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
Click to expand...
 
Back
Top