Copying Headers or Footers within a Workbook

Ross has an existing workbook that contains 36 worksheets. On the first worksheet he set up the footers the way that he wants. Ross wonders if there is an easy way to copy the footers from that first worksheet to all the other worksheets in the workbook.

It is very easy to do this in Excel. All you need to do is to follow these steps:

1. Select your first worksheet, the one that has the headers and footers just the way you want.
2. Now, in the worksheet tab area at the bottom of the Excel window, hold down the Shift key as you click on the tab for the last worksheet you want to affect. You've now created a selection set of worksheets, and your first worksheet should still be the active one. (You can tell this because it has the green underline on the tab itself.) (See Figure 1.)



Figure 1. A selection set of worksheet tabs.

3. Display the Page Layout tab of the ribbon.
4. Click the small icon at the bottom-right of the Page Setup group. Excel displays the Page Setup dialog box.
5. Make sure the Header/Footer tab is displayed. The headers and footers shown in the dialog box should reflect those of the active worksheet, which is the worksheet you first selected in step 1.
6. Click OK. All of the worksheets you selected in step 2 are still selected.
7. Click any worksheet tab other than the active one (the one that has the green underline). This cancels your selection set.

That's it; the headers and footers from the active worksheet are copied to all the worksheets you selected. Any previous headers and footers on the selected worksheets are overwritten in the process.

The key to success in these steps is to make sure that the active worksheet is the one that has your desired headers and footers. This is the worksheet that you should have selected in step 1 and the one that has the green underline on the worksheet tab, as described in step 2. It is that worksheet's headers and footers you should see on the Header/Footer tab of the Page Layout dialog box in step 5. (If you don't see the proper headers and footers in step 5, you should immediately press Esc or click on Cancel in the dialog box so you don't copy the headers and footers.)

If you want to only copy footers (and leave headers as they are, which Ross seems to desire), then you will need to resort to using a macro. Here's a simple macro that will handle any text in the footers only:

Sub CopyFooters()
    Dim wSource As Worksheet
    Dim sSourceName As String
    Dim bFP As Boolean
    Dim bEP As Boolean
    Dim wSFP As Object
    Dim wSEP As Object
    Dim w As Worksheet

    Set wSource = ActiveWorkbook.Worksheets("Sheet1")
    sSourceName = wSource.Name
    Set wSFP = wSource.PageSetup.FirstPage
    Set wSEP = wSource.PageSetup.EvenPage

    With wSource.PageSetup
        bFP = .DifferentFirstPageHeaderFooter
        bEP = .OddAndEvenPagesHeaderFooter
    End With

    For Each w In ActiveWorkbook.Worksheets
        With w.PageSetup
            .DifferentFirstPageHeaderFooter = bFP
            .OddAndEvenPagesHeaderFooter = bEP

            .LeftFooter = wSource.PageSetup.LeftFooter
            .CenterFooter = wSource.PageSetup.CenterFooter
            .RightFooter = wSource.PageSetup.RightFooter

            If bFP Then
                With .FirstPage
                    .LeftFooter.Text = wSFP.LeftFooter.Text
                    .CenterFooter.Text = wSFP.CenterFooter.Text
                    .RightFooter.Text = wSFP.RightFooter.Text
                End With
            End If

            If bEP Then
                With .EvenPage
                    .LeftFooter.Text = wSEP.LeftFooter.Text
                    .CenterFooter.Text = wSEP.CenterFooter.Text
                    .RightFooter.Text = wSEP.RightFooter.Text
                End With
            End If
        End With
    Next w
End Sub

If you examine the code closely, you'll note that there is a difference in how the footer contents are referenced based on whether the footer is a regular footer, a first-page footer, or an even-page footer. Specifically, the non-regular footers use the .Text property, whereas the regular footers don't. This is because (apparently) VBA defaults to using the .Text property when you are using the .LeftFooter, .CenterFooter, and .RightFooter properties of the .PageSetup object. However, if you use those same footer properties with either the .FirstPage or .EvenPage objects, then the .Text property is not assumed—it must be specifically stated. Without it being specified as shown here, the macro will crash.

If you want to use the macro, all you need to do is to make sure the "Sheet1" reference is changed to the name of the worksheet from which you want to copy the footers.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13902) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.