Written by Allen Wyatt (last updated March 2, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Excel allows you to specify headers and footers for your worksheets. You may want to copy these headers and footers from one worksheet to another. Doing so within a workbook is relatively easy, but doing so from one workbook to another can be more daunting.
If the header and footer is one you use quite a bit in new workbooks, and your main concern is to have the header and footer available in those new workbooks (not in existing workbooks), then the best approach would be to create a template workbook. Just set up a workbook as desired, including the specification of headers and footers. Then, save the workbook as an Excel template (XLTX format). You can then create your workbooks based on this template and it will have the headers and footers you desire.
One way to copy headers and footers from a worksheet in one workbook to a worksheet in another is to use the traditional editing methods of copying and pasting. In other words, you can select the header material you want to copy, press Ctrl+C, display the header in the target worksheet, and then press Ctrl+V. The drawback to this approach is that it can involve quite a few steps. After all, there are three sections (left, center, and right) for each header and three for each footer. This means that you must do six copy and paste operations to copy the complete header and footer.
Another way to copy headers and footers from one workbook to another involves the use of native Excel commands to make copies of worksheets. Follow these steps:
Figure 1. The Move or Copy dialog box.
Figure 2. The Header/Footer tab of the Page Setup dialog box.
What you essentially did is to copy the worksheet containing the header and footer you desired, then you copied that header and footer to other worksheets in the workbook, then you deleted the original worksheet.
While these steps work fine, they can be tedious if you need to copy headers and footers to a number of different workbooks. In this case, using a macro to do the copying is the saner approach. The following two macros can be used to copy headers and footers in one simple step. All you need to do is display the source worksheet and use the GetHeaders macro. This macro copies the header and footer information to string variables. You can then display, in turn, each worksheet that you want to have the same header and footer and run the DoHeaders macro.
Option Explicit Dim strHeadLeft As String Dim strHeadCenter As String Dim strHeadRight As String Dim strFootLeft As String Dim strFootCenter As String Dim strFootRight As String Dim bGotHeaders As Boolean Sub GetHeaders() With ActiveSheet.PageSetup strHeadLeft = .LeftHeader strHeadCenter = .CenterHeader strHeadRight = .RightHeader strFootLeft = .LeftFooter strFootCenter = .CenterFooter strFootRight = .RightFooter bGotHeaders = True End With End Sub
Sub DoHeaders() If bGotHeaders Then With ActiveSheet.PageSetup .LeftHeader = strHeadLeft .CenterHeader = strHeadCenter .RightHeader = strHeadRight .LeftFooter = strFootLeft .CenterFooter = strFootCenter .RightFooter = strFootRight End With Else MsgBox "Select the sheet with the " _ & "headers you want to copy," _ & vbCrLf & "then run 'GetHeaders'", _ vbExclamation, "No Headers In Memory" End If End Sub
You could even assign the macros to toolbar buttons or to the Quick Access toolbar, if desired, which can make them even handier for copying headers and footers.
If you have quite a few worksheets and workbooks into which you want the headers and footers copied, there is a different macro approach you can use. The following macro will copy the headers and footers from the active worksheet to all other worksheets in all other open workbooks.
Sub CopyHeaderFooter() Dim PS As PageSetup Dim WB As Workbook Dim WS As Worksheet Set PS = ActiveSheet.PageSetup For Each WB In Workbooks For Each WS In WB.Worksheets With WS.PageSetup .LeftHeader = PS.LeftHeader .CenterHeader = PS.CenterHeader .RightHeader = PS.RightHeader .LeftFooter = PS.LeftFooter .CenterFooter = PS.CenterFooter .RightFooter = PS.RightFooter End With Next Next End Sub
In other words, if you want to copy headers and footers from the current worksheet to 150 other worksheets spread across 15 different workbooks, all you need to do is open the 15 workbooks at the same time, display the source worksheet, and run the macro.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12519) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Copying Headers and Footers.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Yes, Excel can work with Roman numerals, and it even provides a worksheet function that converts to them. How you use ...
Discover MoreIf you want to copy headers or footers from one worksheet to another, it is a relatively easy process, as described in ...
Discover MoreEver wish that you could create nice, long footers that appear at the bottom of each page when you print your worksheet? ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-03-04 20:28:35
@J. Woolley: Nice formatting of the code. I think everyone will appreciate it.
2024-03-03 11:14:08
J. Woolley
The Tip's macros neglect PageSetup's DifferentFirstPageHeaderFooter and OddAndEvenPagesHeaderFooter properties. CopyHeaderFooter also ignores Chart sheets and unnecessarily copies the active sheet's headers and footers to itself. Here is an improved version of CopyHeaderFooter:
Sub CopyHeaderFooter2() 'ignore HeaderPictures and FooterPictures
    Dim PS As PageSetup, WB As Workbook, Sheet As Object
    Dim FP As Object, EP As Object, bFP As Boolean, bEP As Boolean
    Set PS = ActiveSheet.PageSetup
    With PS
        bFP = .DifferentFirstPageHeaderFooter
        If bFP Then Set FP = .FirstPage
        bEP = .OddAndEvenPagesHeaderFooter
        If bEP Then Set EP = .EvenPage
    End With
    For Each WB In Workbooks
        For Each Sheet In WB.Sheets
            If Sheet Is ActiveSheet Then Exit For
            With Sheet.PageSetup
                .LeftHeader = PS.LeftHeader
                .CenterHeader = PS.CenterHeader
                .RightHeader = PS.RightHeader
                .LeftFooter = PS.LeftFooter
                .CenterFooter = PS.CenterFooter
                .RightFooter = PS.RightFooter
                .DifferentFirstPageHeaderFooter = bFP
                .OddAndEvenPagesHeaderFooter = bEP
                If bFP Then
                    With .FirstPage
                        .LeftHeader.Text = FP.LeftHeader.Text
                        .CenterHeader.Text = FP.CenterHeader.Text
                        .RightHeader.Text = FP.RightHeader.Text
                        .LeftFooter.Text = FP.LeftFooter.Text
                        .CenterFooter.Text = FP.CenterFooter.Text
                        .RightFooter.Text = FP.RightFooter.Text
                    End With
                End If
                If bEP Then
                    With .EvenPage
                        .LeftHeader.Text = EP.LeftHeader.Text
                        .CenterHeader.Text = EP.CenterHeader.Text
                        .RightHeader.Text = EP.RightHeader.Text
                        .LeftFooter.Text = EP.LeftFooter.Text
                        .CenterFooter.Text = EP.CenterFooter.Text
                        .RightFooter.Text = EP.RightFooter.Text
                    End With
                End If
            End With
        Next Sheet
    Next WB
    MsgBox "Headers and footers were copied from the active sheet " _
        & "to all sheets of all open workbooks.", vbInformation
End Sub
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments