Code help!

[gbailey1996]

I have created a long code that runs great on a single sheet in my workbook. I would like to be able to run it on multiple sheets at a time within the work book. The sheets I would run it on are all set up the same. I currently have to change the sheet name in 2 places in the code for it run on a different worksheet.

Sub ChangeHeaderDates()
'
' ChangeHeaderDates and PatternTint Macro
'
'
    Sheets("Sheet1").Select
    Range("A6:W6").Select
    Selection.Copy
    Sheets("Elizabeth").Select
    Range("B1").Select
    ActiveSheet.Paste
    Range("B24").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=24
    Range("B47").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=24
    Range("B70").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=36
    Range("B93").Select
    ActiveSheet.Paste
    Range("B116").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=27
    Range("B139").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=27
    Range("B162").Select
     ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=33
    Range("B185").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=24
    Range("B208").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=39
    Range("B231").Select
    ActiveSheet.Paste
   ActiveWindow.SmallScroll Down:=39
    Range("B254").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=27
    Range("B277").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=24
    Range("B300").Select
    ActiveSheet.Paste
        Sheets("Sheet1").Select
    Range("I28:K28").Select
    Selection.Copy
    Sheets("Elizabeth").Select
    Range("B338:D338").Select
    ActiveSheet.Paste
    Range("S1:S322").Select
    With Selection.Interior
        .Pattern = xlLightUp
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799951170384838
        .PatternTintAndShade = 0
    End With
End Sub

 

[shg]

Sub ChangeHeaderDates()
  With Worksheets("Elizabeth")
    Worksheets("Sheet1").Range("A6:W6").Copy
    .Range("B1").PasteSpecial
    .Range("B24").PasteSpecial
    .Range("B47").PasteSpecial
    .Range("B70").PasteSpecial
    .Range("B93").PasteSpecial
    .Range("B116").PasteSpecial
    .Range("B139").PasteSpecial
    .Range("B162").PasteSpecial
    .Range("B185").PasteSpecial
    .Range("B208").PasteSpecial
    .Range("B231").PasteSpecial
    .Range("B254").PasteSpecial
    .Range("B277").PasteSpecial
    .Range("B300").PasteSpecial

    Worksheets("Sheet1").Range("I28:K28").Copy
    .Range("B338:D338").PasteSpecial
    With .Range("S1:S322").Interior
      .Pattern = xlLightUp
      .PatternColorIndex = xlAutomatic
      .ThemeColor = xlThemeColorAccent1
      .TintAndShade = 0.799951170384838
      .PatternTintAndShade = 0
    End With
  End With
End Sub

Which sheet name changes?

 

[shg]

Or, more compactly,

Sub ChangeHeaderDates()
  With Worksheets("Elizabeth")
    Worksheets("Sheet1").Range("A6:W6").Copy
    .Range("B1,B24,B47,B70,B93,B116,B139,B162,B185,B208,B231,B254,B277,B300)").PasteSpecial

    Worksheets("Sheet1").Range("I28:K28").Copy .Range("B338:D338")
    With .Range("S1:S322").Interior
      .Pattern = xlLightUp
      .PatternColorIndex = xlAutomatic
      .ThemeColor = xlThemeColorAccent1
      .TintAndShade = 0.8
      .PatternTintAndShade = 0
    End With
  End With
End Sub