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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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!
 
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
 
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?
 
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
 
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.
 
Your answer was nothing less than brilliant! Thanks!


Gord Dibben said:
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?
Click to expand...
 
Back
Top