VBA-Excel macro only works on current sheet

Question

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

stackoverflow.com/questions/28439376/… You aren't using your With block — Tim Williams

Scott Craner Scott Craner · Accepted Answer · 2018-08-28T22:14:47

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

VBA-Excel macro only works on current sheet

1 Answers