Excluding Named Worksheets from a Loop

Question

I have columns we are tracking for construction purposes.
There are about 70 sheets based on the same template (I'll call them Standard Sheets) that contain data for each column. Every Standard Sheet has the same column widths.

The first three sheets in the workbook, titled "Column Index", "Column List", and "Template", (I'll call these Non-Standard Sheets), have different column widths when compared to the other sheets in the workbook.

I'm trying to create a macro that will resize the column widths of a range of columns on every Standard Sheet throughout the workbook, in the case that my project manager wants a set of columns to be wider than they currently are to fit better on a page.

I'm trying to automate the task rather than manually resizing columns on 70-odd sheets. I would like to exclude Non-Standard Sheets.

I went through dozens of examples excluding sheets by name and tried using them.

I tried Select Case, If statements, and If <...> Not statements throughout the code, using actual sheet names and VBA Project Browser sheet names.

The best way may be to use a range of worksheets numbered using an integer, starting with the first Standard Sheet, but I can't figure out how to do it. It's also possible I'm accidentally calling the entirety of the workbook through a later line of code.

My Non-Standard Sheets are named the following in the VBA project browser.
"Column Index" is "Sheet1" – This sheet is a Summary Sheet that compiles data from the Standard Sheets
"Column List" is "Sheet2" – This sheet contains a list of all of the Standard Sheet names
"Template" is "Sheet 4" – This sheet is the template for a Standard Sheet

I deleted and created worksheets, so the actual name for the first Standard Sheet is "Sheet5" in the VBA project browser, and the last Standard Sheet is "Sheet79" in the VBA project browser, in case one of y'all tries to use a range of numbers.

Sub ChangeChosenColumnSizesToMatchLargest()

Dim ws As Worksheet
Dim c As Integer
Dim m As Variant
Dim ColumnWidth As Variant

For Each ws In ActiveWorkbook.Worksheets               'For all sheets
    
    If ws.Name <> "Column Index" And "Column List" And "Template" Then 'Except Non-Standard                  
    
        For c = 7 To 10                                'For columns G through J

            m = 0                                      'Reset the column width

            For Each w In Worksheets                   'Check all Standard Sheets
                    
                If w.Columns(c).ColumnWidth > m Then   'If a column is bigger than m
                m = w.Columns(c).ColumnWidth           'Reset it           
                End If

            Next w                                     'Check next Standard Sheet

            For Each w In Worksheets                   'Check all Standard Sheets

                If w.Columns(c).ColumnWidth = 0 Then   'Once a column is reset
                w.Columns(c).ColumnWidth = m           'Change width to new width
                End If

            Next w                                     'Check next Standard Sheet    
                             
        Next c                                         'Check next column 
                                    
    End If

Next ws                                                'Check next Standard Sheet

End Sub

Answer 1

Resize Column Widths Excluding Specific Worksheets

Sub ResizeColumnWidthsToLargest()
    
    ' Define constants.
    Const ADJUST_COLUMNS As String = "G:J"
    Dim SHEETS_TO_EXCLUDE() As Variant:
    SHEETS_TO_EXCLUDE = Array("Column Index", "Column List", "Template")
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Create/reference a new instance of the VBA collection
    ' to hold the required worksheet objects collected in the 1st loop
    ' to be utilized in the 2nd loop.
    Dim coll As Collection: Set coll = New Collection
    
    ' Declare additional variables.
    Dim ws As Worksheet, crg As Range, MaxColumnWidth As Double
    
    ' Determine the largest column width.
    For Each ws In wb.Worksheets
        If IsError(Application.Match(ws.Name, SHEETS_TO_EXCLUDE, 0)) Then
            For Each crg In ws.Columns(ADJUST_COLUMNS).Columns
                If crg.ColumnWidth > MaxColumnWidth Then
                    MaxColumnWidth = crg.ColumnWidth
                End If
            Next crg
            coll.Add ws
        End If
    Next ws
    
    ' Exit if no worksheet found.
    If coll.Count = 0 Then Exit Sub
    
    ' Resize the column widths to the largest column width.
    For Each ws In coll
        ws.Columns(ADJUST_COLUMNS).ColumnWidth = MaxColumnWidth
    Next ws
    
    ' Inform.
    MsgBox "Column widths adjusted to largest.", vbInformation

End Sub