Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Looping Through Sheets (https://www.askmehelpdesk.com/showthread.php?t=380499)

  • Jul 27, 2009, 03:14 PM
    adalton
    Looping Through Sheets
    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.
  • Jul 27, 2009, 05:37 PM
    JBeaucaire

    Here's a sample to whet your appetite, you can adjust/play from there:
    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

    (source)

    Or...
    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

    (source)

  • All times are GMT -7. The time now is 02:05 AM.