Loop Through All Worksheets Except Those Named

Question

The below code is intended to format column width, alignment and formatting on all worksheets in my workbook apart from those listed below, namely:

"Contents Page", "Completed", "VBA_Data", "Front Team Project List", "Mid Team Project List", "Rear Team Project List", "Acronyms".

However the code only appears to run on the ActiveSheet, which is "Contents_Page":

Public Sub SheetCleanup()
Application.ScreenUpdating = False
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
Select Case sh.Name
Case Is = "Contents Page", "Completed", "VBA_Data", "Front Team Project List", "Mid Team Project 
List", "Rear Team Project List", "Acronyms"

Case Else
        ActiveSheet.Columns("g:g").NumberFormat = "dd-mm"
        ActiveSheet.Columns("i:i").NumberFormat = "0"
        ActiveSheet.Columns("B:K").HorizontalAlignment = xlLeft
        ActiveSheet.Columns("G:G").HorizontalAlignment = xlCenter
        ActiveSheet.Columns("A:A").HorizontalAlignment = xlCenter
        ActiveSheet.Columns("H:H").HorizontalAlignment = xlCenter
        ActiveSheet.Columns("I:I").HorizontalAlignment = xlCenter
        ActiveSheet.Columns("J:J").HorizontalAlignment = xlCenter
        ActiveSheet.Columns("A").ColumnWidth = 27
        ActiveSheet.Columns("B").ColumnWidth = 50
        ActiveSheet.Columns("C").ColumnWidth = 50
        ActiveSheet.Columns("D").ColumnWidth = 21
        ActiveSheet.Columns("E").ColumnWidth = 27
        ActiveSheet.Columns("F").ColumnWidth = 21
        ActiveSheet.Columns("G").ColumnWidth = 20
        ActiveSheet.Columns("H").ColumnWidth = 18
        ActiveSheet.Columns("I").ColumnWidth = 25
        ActiveSheet.Columns("J").ColumnWidth = 24
        ActiveSheet.Rows("3").HorizontalAlignment = xlCenter

End Select
Next sh

End Sub

score 0 · Accepted Answer · answered Feb 05 '20 at 08:08

The ActiveSheet is not changed in the loop, thus it performs actions through it. In the code below, it performs actions on the sh, which is changed.

In general, consider changing ActiveWorkbook to ThisWorkbook and see How to avoid using Select in Excel VBA

Public Sub SheetCleanup()

    Application.ScreenUpdating = False
    Dim sh As Worksheet

    For Each sh In ActiveWorkbook.Worksheets
        Select Case sh.Name
        Case Is = "Contents Page", "Completed", "VBA_Data", "Front Team Project List", "Mid Team Project List", "Rear Team Project List", "Acronyms"

        Case Else
            With sh
                .Columns("g:g").NumberFormat = "dd-mm"
                .Columns("i:i").NumberFormat = "0"
                .Columns("B:K").HorizontalAlignment = xlLeft
                .Columns("G:G").HorizontalAlignment = xlCenter
                .Columns("A:A").HorizontalAlignment = xlCenter
                .Columns("H:H").HorizontalAlignment = xlCenter
                .Columns("I:I").HorizontalAlignment = xlCenter
                .Columns("J:J").HorizontalAlignment = xlCenter
                .Columns("A").ColumnWidth = 27
                .Columns("B").ColumnWidth = 50
                .Columns("C").ColumnWidth = 50
                .Columns("D").ColumnWidth = 21
                .Columns("E").ColumnWidth = 27
                .Columns("F").ColumnWidth = 21
                .Columns("G").ColumnWidth = 20
                .Columns("H").ColumnWidth = 18
                .Columns("I").ColumnWidth = 25
                .Columns("J").ColumnWidth = 24
                .Rows("3").HorizontalAlignment = xlCenter
            End With
        End Select
    Next sh

End Sub

Loop Through All Worksheets Except Those Named

1 Answers1