Trouble Excluding Named Worksheets from a Loop

At my workplace, I have a database of column we are tracking for construction purposes. There are about 70 sheets that are all based on the same template (I'll call them Standard Sheets) that contain data for each column. Every Standard Sheets has the same column widths as every other Standard Sheet. The first three sheets in the workbook, however, titled "Column Index", "Column List", and "Template", (I'll call these sheets my 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 decides that they want a set of columns to be wider than they currently are to fit better on a page (this has happened before, so I'm trying to automate the task rather than have to manually resize columns on 70-odd sheets). I would like this macro to exclude Non-Standard Sheets.

I went through dozens of examples excluding sheets by name and tried using them, but I'm clearly doing something wrong. I've tried using Select Case, If statements, and If <...> Not statements throughout the code, using actual sheet names and VBA Project Browser sheet names, all to no avail. I think the best way to do it may be to use a range of worksheets numbered using an integer, starting with the first Standard Sheet, but I can't seem to figure out how to do it. It's also entirely possible I'm accidentally calling the entirety of the workbook through a later line of code.

I know 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've 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. Here's what I've got so far:

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

Feel free to completely redesign the code and/or use other methods—I'm not married to this structure.

Thanks in advance!

Tomás

Solution

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