Is there a way to loop through sheets in a workbook. I would like to add a header/footer to each sheet in a workbook and then apply the header/footer macro to each sheet.
![]() |
Is there a way to loop through sheets in a workbook. I would like to add a header/footer to each sheet in a workbook and then apply the header/footer macro to each sheet.
Here's a sample to whet your appetite, you can adjust/play from there:
(source)Code:Sub InsertHeaderFooter()
' inserts the same header/footer in all worksheets
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Changing header/footer in " & ws.Name
With ws.PageSetup
.LeftHeader = "Company name"
.CenterHeader = "Page &P of &N"
.RightHeader = "Printed &D &T"
.LeftFooter = "Path : " & ActiveWorkbook.Path
.CenterFooter = "Workbook name &F"
.RightFooter = "Sheet name &A"
End With
Next ws
Set ws = Nothing
Application.StatusBar = False
End Sub
Or...
(source)Code:Sub setFooter()
Dim mySht As Variant
For Each mySht In Worksheets
mySht.Activate
ActiveSheet.PageSetup.LeftFooter = _
Format(Worksheets("SetUp").Range("A1").Value)
ActiveSheet.PageSetup.RightFooter = _
Format(Worksheets("SetUp").Range("B1").Value, "mmddyy")
Next
End Sub
All times are GMT -7. The time now is 12:02 AM. |