[adalton]

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.

[JBeaucaire]

Here's a sample to whet your appetite, you can adjust/play from there:

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

(source)

Or...

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

(source)