CODEWITHSUNDEEP
excelvba
luke
luke

Reputation: 482

Hide & unhide columns on all sheets with If function VBA

I am trying to hide/unhide specific columns on only one sheet named VPL and then hide/unhide a different set of specific columns on the remaining sheets in the workbook.

Here is the code I've got so far but it's only working on the sheet named VPL and is not hiding the columns on any other sheets when looping through the remaining sheets in the workbook.

    Sub HideAndUnHideProduct2()

'Are you sure you want to run this macro, when you run this a box will popup and ask yes or no
Dim varResponse As Variant
varResponse = MsgBox("This will Hide/UnHide Product 2 on All Sheets, Do You Want To Continue", vbYesNo, "Selection")
If varResponse <> vbYes Then Exit Sub

Application.ScreenUpdating = False

'Hides/UnHides Product columns on all sheets

If VPL.Columns("L:N").Hidden = True Then

'UnHides Specified columns On Specified Sheet
VPL.Columns("L:N").EntireColumn.Hidden = False 'Unhides Selected Colunms

'UnHides columns On All Sheets Except The Ones Specified Below
 Dim wsU As Worksheet
For Each wsU In Sheets
If wsU.Name <> "VPL" Then '<Sheets To Be Skiped From Loop
        'Code To Be Looped below
Columns("L:M").EntireColumn.Hidden = False 'UnHides Selected Colunms
    'End of Code To Be Looped
    End If
Next wsU

Else

'Hides Specified columns On Specified Sheet
VPL.Columns("L:N").EntireColumn.Hidden = True

'Hides columns On All Sheets Except The Ones Specified Below
Dim wsH As Worksheet
For Each wsH In Sheets
If wsH.Name <> "VPL" Then '<Sheets To Be Skiped From Loop
        'Code To Be Looped below
Columns("L:M").EntireColumn.Hidden = True 'Hides Selected columns
    'End of Code To Be Looped
    End If
Next wsH
End If
Application.ScreenUpdating = True

End Sub

Any help is greatly appreciated as I am not very good in vba

Upvotes: 1

Views: 1396

Answers (1)

Matt Cremeens
Matt Cremeens

Reputation: 5151

Without a sheet reference, your code

Columns("L:M").EntireColumn.Hidden = False

will only refer to the active sheet. Since you are looping through multiple sheets, you need to reference the sheet variable in your for-loop, like so

wsU.Columns("L:M").EntireColumn.Hidden = False

similarly for

wsH.Columns("L:M").EntireColumn.Hidden = True

Upvotes: 2

Related Questions