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


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?
 
Ad

Advertisements

G

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
 
T

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!
 
G

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
 
B

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?
 
Ad

Advertisements

G

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
 
B

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

Advertisements

Joined
Aug 27, 2012
Messages
1
Reaction score
0
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top