Apply VBA on specific sheets

[darcypred]

Hi everyone,
I don't know much about VBA, but I am trying to hide rows on multiple sheets without having to go through each sheet and run the macro. Here is my code:
Sub HideRows()
Dim cell As Range
For Each cell In Range("A1:A30")
If UCase(cell.Value) = "NO" Then
cell.EntireRow.Hidden = True
End If
Next
End Sub
And I want to run this on sheets 1, 27, 28, 31, 32, 37, 38, 39, and 45.
Thank you for you help!

 

[Fluff]

Hi & welcome to MrExcel.
Are those the sheet names, or the index numbers?

 

[Joe4]

Welcome to the Board!

Try this:

Sub HideRows()

    Dim cell As Range
    Dim shts
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    shts = Array(1, 27, 28, 31, 32, 37, 38, 39, 45)
    
    For i = LBound(shts) To UBound(shts)
        Sheets(shts(i)).Activate
        For Each cell In Range("A1:A30")
            If UCase(cell.Value) = "NO" Then
                cell.EntireRow.Hidden = True
            End If
        Next cell
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

 

[darcypred]

Hi & welcome to MrExcel.
Are those the sheet names, or the index numbers?

Thank you! Those are the Index numbers

 

[darcypred]

Welcome to the Board!

Try this:

Sub HideRows()

    Dim cell As Range
    Dim shts
    Dim i As Long
   
    Application.ScreenUpdating = False
   
    shts = Array(1, 27, 28, 31, 32, 37, 38, 39, 45)
   
    For i = LBound(shts) To UBound(shts)
        Sheets(shts(i)).Activate
        For Each cell In Range("A1:A30")
            If UCase(cell.Value) = "NO" Then
                cell.EntireRow.Hidden = True
            End If
        Next cell
    Next i
   
    Application.ScreenUpdating = True
   
End Sub

I input this and it hid rows on Sheet 1 but not on any of the other sheets

 

[Marc L]

Hi Darcy,​

​

as it could be easier to help with an attachment (dropbox or any other files hosting website) …​

 

[Fluff]

I input this and it hid rows on Sheet 1 but not on any of the other sheets

Did you get any error messages?

 

[Joe4]

I input this and it hid rows on Sheet 1 but not on any of the other sheets

Where exactly did you place the code?
Did you put it in a General Module, or the Sheet1 module?
It should be in a General Module.

 

[dmt32]

Hi,
try this update to your code and see if helps

Sub HideRows()

    Dim cell    As Range
    Dim i       As Long
    
    For i = 1 To 9
        With Worksheets(Choose(i, 1, 27, 28, 31, 32, 37, 38, 39, 45))
        For Each cell In .Range("A1:A30")
                cell.EntireRow.Hidden = UCase(cell.Value) = "NO"
        Next cell
        End With
    Next i
End Sub

Code assumes that the numbers you have provided are the sheets index number

If though, they are the sheets name then try this version

Sub HideRows()

    Dim cell    As Range
    Dim i       As Long
    
    For i = 1 To 9
        With Worksheets(CStr(Choose(i, 1, 27, 28, 31, 32, 37, 38, 39, 45)))
        For Each cell In .Range("A1:A30")
                cell.EntireRow.Hidden = UCase(cell.Value) = "NO"
        Next cell
        End With
    Next i
End Sub

Hope helpful

Dave