gbailey1996
New Member
- Joined
- Oct 31, 2016
- Messages
- 2
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.
Code:
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