Want to apply vba code to selected sheet in one go.

[Harshil Mehta]

The code dynamically hides rows & columns. I want to apply this code in one go to multiple sheets.
Sheets Names are based on cell value which means if the value in the cell changes, sheet name also changes.
I am new to VBA coding, would be thankful if anyone could help me out with this.

Sub Hide_Rows_Columns()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets(Array(Sheets(2), Sheets(3), Sheets(4)))

With Range("B:B")
Range(.Cells(Rows.Count, 2), .Cells(Rows.Count, 2).End(xlUp).Offset(2, 0)).EntireRow.Hidden = True
End With

With Range("10:10")
Range(.Cells(1, .Columns.Count), .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 2)).EntireColumn.Hidden = True
End With

Next

End Sub

 

[davesexcel]

Hello,
Where would the sheet names be?

 

[Harshil Mehta]

Hello,
Where would the sheet names be?

 

[Harshil Mehta]

Hello,
Where would the sheet names be?

In the User_Dashboard sheet, in cell B2, B3, B4 & B5 for their respective sheet no. Have uploaded a snapshot for you reference.

 

[davesexcel]

This should get it done.

Sub Button1_Click()
    Dim ws As Worksheet, sh As Worksheet
    Dim rng As Range, c As Range

    Set ws = Sheets("User_Dashboard")
    With ws
        Set rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For Each c In rng.Cells
            For Each sh In Sheets
                If sh.CodeName = c Then
                    With sh
                        .Name = c.Offset(, 1)
                        With .Range("B:B")
                            Range(.Cells(.Rows.Count, 2), .Cells(.Rows.Count, 2).End(xlUp).Offset(2, 0)).EntireRow.Hidden = True
                        End With
                        With .Range("10:10")
                            Range(.Cells(1, .Columns.Count), .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 2)).EntireColumn.Hidden = True
                        End With
                    End With
                End If
            Next sh
        Next c
    End With



End Sub