VBA – Group sheets based on their names

Posted on December 21, 2013 by

Sometimes you simply want to do an action in a file to all sheets, but not to the two important ones, named “Important1” and “Important2”. In this case, you may use a simple for/each loop with a select case option. In the current example, we will group all sheets in the excel file without the ones with the name “Imp1” and “Imp2”.

Sub hideSheet()
    Dim sh
    On Error Resume Next

    For Each sh In Worksheets
          Select Case (ws.CodeName)
            Case "Imp1", "Imp2"
            Case Else
                sh.Activate
                Columns(1, 15).Select
                Selection.ClearOutline

                Range("AD:AZ").EntireColumn.Hidden = True
                Columns("C:AB").Select
                Selection.Columns.Group
                ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1

                Range("AC1").Select
          End Select
    Next
End Sub

Sub hideSheet()

Dim sh

On Error Resume Next

For Each sh In Worksheets

Select Case (ws.CodeName)

Case "Imp1", "Imp2"

Case Else

sh.Activate

Columns(1, 15).Select

Selection.ClearOutline

Range("AD:AZ").EntireColumn.Hidden = True

Columns("C:AB").Select

Selection.Columns.Group

ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1

Range("AC1").Select

End Select

End Sub