How do I copy a header and footer to multiple worksheets?

[Guest]

I have a worksheet that has a custom header and footer and I need to copy it
to 40 worksheets, is it possible to do without goining into each worksheet
individually and setting it up? I am using Excel 2003. Has anybody
encountered this problem?

 

[Gord Dibben]

Select the sheet with the custom header and footer.

Right-click on the sheet tab and "select all sheets".

Go into File>Page Setup>Headers and Footers.

Click on OK without doing anything else.

All sheets now have that header/footer.

Right-click and "ungroup sheets" or select any sheet but the active sheet.


Gord Dibben MS Excel MVP

 

[Guest]

Very useful and so simple. Many thanks for this post, i can use the method

 

[Teresa]

Hi, I tried this on my workbook and it worked great EXCEPT it changed the
page layout of all the worksheets. I had each worksheet set up the way I
needed it to be (portrait/letter;landscape/legal;etc). After following the
instructions to get a common header on each page, the worksheets print
layouts were all changed too. Is there a way to do this and ONLY affect the
header/footer? Thanks!

 

[Gord Dibben]

Hope you didn't save the workbook<g>

To just affect the headers/footers and not the rest of the page setup you will
have to use a macro to set the headers/footers.

Here is one to set the centerfooter for all sheets.

Sub Path_All_Sheets()
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.RightFooter = ActiveWorkbook.FullName & " " & Chr(13) _
& Application.UserName & " " & Date
Next
End Sub

And a variation using a cell value

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.PageSetup.CenterFooter = "&""Algerian,Regular""&16" _
& ws.Range("A1").value
Next


Gord

 

[Bob Arnett]

I'm actually trying to do what Teresa accidently did and that is repeat all
the page setup settings on all worksheets. Unfortunately, in Excel 2007, I
can't seem to find a way to change the page setup that will 'take' on all
worksheets in the selected sheets. Is there another way to do this in Excel
2007?

 

[Gord Dibben]

See Ron de Bruin's site for where to find the 2007 commands.

I don't have 2007 so can't tell you how to do much but I think you can still
group sheets and do a page setup for all.


Gord

 

[Bob Arnett]

Well, although, that was exactly what I thought I had previously done, I went
back and did that again and this time it worked. I'm not sure what I did
differently but at least it did do what I wanted. Thanks for the help.

 

[Jack45]

Your answer was nothing less than brilliant! Thanks!

Select the sheet with the custom header and footer.

Right-click on the sheet tab and "select all sheets".

Go into File>Page Setup>Headers and Footers.

Click on OK without doing anything else.

All sheets now have that header/footer.

Right-click and "ungroup sheets" or select any sheet but the active sheet.


Gord Dibben MS Excel MVP

On Tue, 8 Aug 2006 12:16:02 -0700, ssr5 <[email protected]> wrote:

>I have a worksheet that has a custom header and footer and I need to copy it
>to 40 worksheets, is it possible to do without goining into each worksheet
>individually and setting it up? I am using Excel 2003. Has anybody
>encountered this problem?