Can't get this Macro to work

[tyler215]

Sub Update_Tabs()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
For Each r In ws.Range("B1:L1")
If r.Value = "No" Then
r.EntireColumn.Hidden = True
Else
r.EntireColumn.Hidden = False
End If
Next ws
End Sub

I am just trying to hide any column that says No in it's first cell on each sheet. I got it to work on just one sheet, but I am messing something up when I tried to add in the apply to all tabs part.

Anybody know what I'm doing wrong?

Thanks in advance

[Mordred]

Hi tyler,

If you indent your code you would have found that you didn't provide a Next r.

Sub Update_Tabs()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each r In ws.Range("B1:L1")
            If r.Value = "No" Then
                r.EntireColumn.Hidden = True
            Else
                r.EntireColumn.Hidden = False
            End If
        Next r
    Next ws
End Sub

Also, and just to go one step further, you can declare r too, much like how you nicely declared ws

Sub Update_Tabs()
    Dim ws As Worksheet
    Dim r As Range
    For Each ws In ThisWorkbook.Worksheets
        For Each r In ws.Range("B1:L1")
            If r.Value = "No" Then
                r.EntireColumn.Hidden = True
            Else
                r.EntireColumn.Hidden = False
            End If
        Next r
    Next ws
End Sub

[tyler215]

Ahhh you just made my day, thank you!

[FDibbins]

tyler, Im happy that you had your question resolved

For future reference, please ensure that your thread title complies with Forum Rule #1 about proper thread titles.

Thanks

[Mordred]

And of course,
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[FDibbins]

lol thanks Mordred, I missed that

[Mordred]

I missed it all originally.

[FDibbins]

I guess I read the title, opened it and skipped to the bottom - saw that the question was answered and didnt look further