Use one Macro Command to run over 46 out of 54 worksheets

[D_Spark]

I have a workbook containing 46 worksheets that are identical in layout design.

On each of the 46 worksheets I have a Command Button

_________

Private Sub Hide_Zero_Rows_Click()
Rows("4:70").Hidden = False
Dim v As Range
For Each u In Range("u4:u70")
u.EntireRow.Hidden = u.Value = 123123123
Next u
End Sub
_______________________


What I need help with is to create one Command Button on a worksheet called "Launch"

then for this button when clicked to run the above command lines over all 46 worksheets

Worksheet names that the command needs to run on are:

A10, A20, A31, A32, A33, A50, B70, B10, B20, B30, B40, B60, C10, C20, C70, C80, C90, E10, E40, E50, E65, E70, E100, E100, E120, E130, E140, E150, E160, E170, E180, F10, F20, F30, F40, F50, G10, G30, G40, G50, G70, G80, H10, H20, H30, I10


Worskeet names that the command needs to ignore are:
OV, WO, VOD, ST, FA, VOI, ARK, LAUNCH

 

[GTO]

Hi there,

Not tested, but try like:

Option Explicit
    
Sub exa()
Dim cell As Range, u As Range
    
    For Each cell In ThisWorkbook.Worksheets("Launch") _
                        .Range("A10, A20, A31, A32, A33, A50, B70, B10, B20, B30, B40, B60," & _
                               "C10, C20, C70, C80, C90, E10, E40, E50, E65, E70, E100, E100," & _
                               "E120, E130, E140, E150, E160, E170, E180, F10, F20, F30, F40," & _
                               "F50, G10, G30, G40, G50, G70, G80, H10, H20, H30, I10")
        With ThisWorkbook.Worksheets(cell.Value)
            .Rows("4:70").Hidden = False
            For Each u In .Range("U4:U70")
                u.EntireRow.Hidden = u.Value = 123123123
            Next
        End With
    Next
End Sub

I see that you dim'd v but refer to u. If yyou use Option Explicit, this would be caught.

 

[D_Spark]

Thanks for this:

the code fails on the line:

With ThisWorkbook.Worksheets(cell.Value)


stating:
Subscript out of range

 

[GTO]

...Worksheet names that the command needs to run on are:

A10, A20, A31, A32, A33, A50, B70, B10, B20, B30, B40, B60, C10, C20, C70, C80, C90, E10, E40, E50, E65, E70, E100, E100, E120, E130, E140, E150, E160, E170, E180, F10, F20, F30, F40, F50, G10, G30, G40, G50, G70, G80, H10, H20, H30, I10...

Hi there,

Presuming you have the code in the workbook that has the sheet named "Launch," I was understanding from the above, that the sheets' names that you want the code to run against are in the listed cells. Is that not correct?

If it is, you might want to check for errant leading/trailing spaces in the cells and/or on the various tabs.