CODEWITHSUNDEEP
excelvba
Dylan Fernando
Dylan Fernando

Reputation: 1

VBA-Excel macro only works on current sheet

Hi all pretty new to VBA here and I am struggling to get my macro to work on multiple sheets. I basically want to hide and unhide columns "B-AB" if they have an "X" in row 8. Currently the macro only works on the active sheet and not the active workbook. Thanks!

Sub roll()

Dim sh As Worksheet
Dim c As Range


For Each sh In ActiveWorkbook.Sheets

With sh

Application.ScreenUpdating = False

Columns("B:AB").Select
Selection.EntireColumn.Hidden = False


        For Each c In Range("b8:ab8").Cells
            If c.Value = "X" Then
            c.EntireColumn.Hidden = True
            End If

        Next c
            End With

Next sh

Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 1717

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

You need to add . in front of the range object that are assigned to the sheet noted in the with:

Sub roll()

Dim sh As Worksheet
Dim c As Range


For Each sh In ActiveWorkbook.Sheets

With sh

    Application.ScreenUpdating = False

    .Columns("B:AB").Hidden = False


        For Each c In .Range("b8:ab8").Cells
            If c.Value = "X" Then
                c.EntireColumn.Hidden = True
            End If
        Next c
    End With

Next sh

Application.ScreenUpdating = True

End Sub

Upvotes: 2

Related Questions