• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Skip macro action if sheet hidden


New Member
Long time Excel admirer, and slowly learning/fumbling my way through macros, I'm hoping someone can help me sort this one out.

Teams will send me excels, with multiple sheets, and depending on the project, some sheets will be hidden as they're N/A, but I need to perform the following steps on the remaining sheets...

Step 1 - Hide specific sheets based on their name.
I managed to get this to work, using the following (dummy sheet names)...

>>> use code - tags <<< instead any indents
Dim wsSheet As Worksheet
    For Each wsSheet In Worksheets
        If wsSheet.Name = "X" Or wsSheet.Name = "Y" Or wsSheet.Name = "Z" Then
            wsSheet.Visible = xlSheetHidden
        End If
    Next wsSheet
End Sub
Step 2 - Hide specific columns based on sheet name.
I have got the below code to work (dummy sheet names), however, if Sheet 'B' was already hidden, the code breaks.
I am trying to get excel to perform simple pre-determined tasks per sheet name, but to skip those tasks if the sheet is hidden (because that sheet was N/A for this project)... hoping someone can tweak this code accordingly? (I tried playing around with 'If sheet A is visible, Then perform action... but my VBA language isn't up to scratch ha)

Selection.EntireColumn.Hidden = True
Columns("A:A").ColumnWidth = 70
Selection.EntireColumn.Hidden = True
Columns("A:A").ColumnWidth = 70
Sheets C, D.... I'll follow the same steps as above with slightly different instructions on which columns to hide
End Sub
Combine into 1 Macro button
Not sure if I should post separately, but I'm hoping once I have the code correct for step 2, I can join that with Step 1, so I just have to hit one button and all actions happen together?

Many thanks for any help you can provide
Last edited by a moderator:

Marc L

Excel Ninja
Hello, as a good enough VBA procedure does not need to Select anything so for starters :​
         Dim Ws As Worksheet
    For Each Ws In Worksheets
        With Ws
            If .Visible = -1 Then
                Select Case .Name
                       Case "A"
                            .Columns(1).ColumnWidth = 70
                            .Range("C:C,E:E,G:BK").EntireColumn.Hidden = True
                       Case "B"
                            .Columns(1).ColumnWidth = 70
                            .Range("C:C,E:E,H:H,J:J").EntireColumn.Hidden = True
                       Case "X", "Y", "Z"
                            .Visible = 0
                End Select
            End If
        End With
Do you like it ? So thanks to click on bottom right Like !​


New Member
Hi @Marc L

Thank you so much for your help, that seems to have worked a treat! :)

I replaced the Case "A" with my actual sheet name, and then created additional Cases for all the other sheets I needed to have instructions for.
I wasn't sure if that's what 'Case X, Y, Z' line was for? so I deleted that line...
but then when I ran the macro it hid the last sheet that had instructions.
So I also deleted the '.Visible = 0' line of code, and that seems to have fixed the problem?

Please let me know if I've made a mistake and Visible=0 still needs to be in there, and I need to adjust further? :oops:

Thank you again, much appreciated

Marc L

Excel Ninja
The Case X, Y, Z block is your Step 1 as you asked to « Combine into 1 Macro button » …​
A must read : the VBA help for Select Case.​