Macro Adjustment

[tommychowdah]

Hi!

I would like to adjust this macro so that instead of being applied to every sheet, it looks up a range of tab names, and is only applied to those tabs. Any help would be much appreciated!

Sub Hide_All_Rows()
Dim r As Range
Dim ws As Worksheet
On Error GoTo err_handler
Application.ScreenUpdating = False
For Each ws In Worksheets
For Each r In ws.Range("ZZ1:ZZ1000")
If r.Value = 1 Then
r.EntireRow.Hidden = True
End If
Next r
Next ws

Application.ScreenUpdating = True
Exit Sub
err_handler:
MsgBox "Sheet: " & ws.Name & " caused an error"
End Sub

 

[Fluff]

How about

Sub Hide_All_Rows()
   Dim r As Range
   Dim Sht As Variant
   
   On Error GoTo err_handler
   Application.ScreenUpdating = False
   For Each Sht In [COLOR=#ff0000]Sheets("Summary").Range("C1", Sheets("Summary").Range("C" & Rows.Count).End(xlUp))[/COLOR]
      For Each r In Sheets(CStr(Sht)).Range("ZZ1:ZZ1000")
         If r.Value = 1 Then r.EntireRow.Hidden = True
      Next r
   Next Sht
   
   Application.ScreenUpdating = True
   Exit Sub
err_handler:
   MsgBox "Sheet: " & Sheets(Sht).Name & " caused an error"
End Sub

Change the part in red to match the sheet & range where you have the sheet names

 

[tommychowdah]

Hi,

Thank you for the response, there are over 100 tabs, so typing each one out is not practical. Can you create a named range and have the macro referenec the named range?

Best,
Chowdah

 

[Fluff]

There's no real difference between what I've supplied & a named range, you still need to list the sheets somewhere.
Do you already have a list of the sheet names you want to include? & if so where are they?

 

[tommychowdah]

All the sheets necessary are on a tab called "List" in range E3:E80

 

[Fluff]

In that case change the line in red (in post#2) to this

Sheets("List").Range("E3", Sheets("List").Range("E" & Rows.Count).End(xlUp))

 

[tommychowdah]

The macro crashes on the error handler, any thoughts?

 

[Fluff]

Remove the error handler & see what line causes the problem & what the error message is.