Apply VBA to a range of worksheets

[JamesWayne]

Hi All

I'm currently using some VBA to hide rows based on value in a cell and what I would like to do is apply that code to a range of worksheets in the workbook, how would I go about this.

Lets say the worksheet names are called Month1, Month2, Month3 etc through to Month12.

This is my code:

Set rng = Range("A5:A90")
For Each cel In rng
If cel.Value = 0 Then
cel.EntireRow.Hidden = True
ElseIf cel.Value = 1 Then
cel.EntireRow.Hidden = False
End If
Next cel

Thank you

 

[BrianJN1]

I ran the VBA on one of my sheets, these two lines performed the task;
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Visible = False

But wait, you said a range of sheets and I haven't supplied that.

Sub Hide_Sheets
Dim a As Long
    For a = 1 To 3
    
    Sheets("Sheet" & a).Select
    ActiveWindow.SelectedSheets.Visible = False
    Next
end sub

That hid 3 sheets.

 

[JamesWayne]

I ran the VBA on one of my sheets, these two lines performed the task;
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Visible = False

But wait, you said a range of sheets and I haven't supplied that.

Sub Hide_Sheets
Dim a As Long
    For a = 1 To 3
    
    Sheets("Sheet" & a).Select
    ActiveWindow.SelectedSheets.Visible = False
    Next
end sub

That hid 3 sheets.

Apologies I meant apply the code I've written that hide rows on the sheet to a range of sheets. Not hide the sheet over a range.

 

[BrianJN1]

Sorry James,
I haven't long come in from a day trip.

How would that work if you deleted "ActiveWindow.SelectedSheets.Visible = False" from my code and copied in your code from comment #1 ?

To me that's selecting sheets and addressing what you require of your code, yes?

 

[MARK858]

Possibly if it is just certain sheets and not all the sheets...

Sub Test1()

    Dim Rng As Range, Cel As Range, Sh As Worksheet
    Application.ScreenUpdating = False

    For Each Sh In Sheets(Array("Sheet1", "Sheet2", "Sheet4")) ' change sheet names to suit

        Set Rng = Sh.Range("A5:A90")
        For Each Cel In Rng
            If Cel.Value = 0 Then
                Cel.EntireRow.Hidden = True
            ElseIf Cel.Value = 1 Then
                Cel.EntireRow.Hidden = False
            End If
        Next Cel
    Next
    Application.ScreenUpdating = True
End Sub