How do you refer to a list of sheets in VBA?-CodePudding

I am extremely new to VBA and recorded a macro but I would like to update the recorded macro to have it dynamically work with multiple sheets as sheet names are currently hard coded.

For example,

Sub FormattingFinal()
    Sheets("Template Format").Select
    Range("A6:G8").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Sheets("Hamilton").Select
    Range("A6").Select
    ActiveSheet.Paste
End Sub

I would like "Template Format" to be hard coded as I always want to copy from this sheet, but change "Hamilton" to loop through a list of sheets that I specify (all sheets in the workbook except for two sheets). How do I go about doing this?

CodePudding user response：

Sub FormattingFinal()
    
    Dim sh As Worksheet
    
    For Each sh In ThisWorkbook.Worksheets
        'change Exclude1 and 2 to the sheets you want
        If sh.Name <> "Exclude1" And sh.Name <> "Exclude2" Then
            'The first argument to Copy is the destination, so you can do it all in one line
            ThisWorkbook.Worksheets("Template Format").Range("A6:G8").Copy sh.Range("A6:G8")
        End If
    Next sh
    
End Sub