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

https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 You aren't using your With block — Tim Williams, Aug 28 '18 at 22:14

score 2 · Answer 1 · answered Aug 28 '18 at 22:14

2

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

answered Aug 28 '18 at 22:14

Scott Craner

148,073
10
49
81

Thanks so much! Works like a charm. I am also wondering if it wasn't working as well since my code wasn't aligned like the way you had it. My last end with wasn't indented aligned with the ".columns" section of code? – Dylan Fernando Aug 29 '18 at 17:40
Alignment has nothing to do with how it works. Just looks better – Scott Craner Aug 29 '18 at 20:18
Thanks so much Scott! – Dylan Fernando Aug 30 '18 at 21:12

VBA-Excel macro only works on current sheet

1 Answers1

Linked