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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ssr5 - 08 Aug 2006 20:16 GMT

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?

Reply to this Message

Gord Dibben - 08 Aug 2006 21:06 GMT

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

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

Reply to this Message

Bill W - 05 Oct 2006 12:21 GMT

Very useful and so simple. Many thanks for this post, i can use the method
on all my worksheets, and save a lot of time

> Select the sheet with the custom header and footer.
>
[quoted text clipped - 14 lines]
> >individually and setting it up? I am using Excel 2003. Has anybody
> >encountered this problem?

Reply to this Message

Teresa - 14 Feb 2008 00:01 GMT

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!

> Select the sheet with the custom header and footer.
>
[quoted text clipped - 14 lines]
> >individually and setting it up? I am using Excel 2003. Has anybody
> >encountered this problem?

Reply to this Message

Gord Dibben - 14 Feb 2008 02:06 GMT

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

>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
[quoted text clipped - 21 lines]
>> >individually and setting it up? I am using Excel 2003. Has anybody
>> >encountered this problem?

Reply to this Message

Bob Arnett - 09 May 2008 21:25 GMT

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?

> 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
[quoted text clipped - 21 lines]
> > >individually and setting it up? I am using Excel 2003. Has anybody
> > >encountered this problem?

Reply to this Message

Gord Dibben - 10 May 2008 19:06 GMT

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

>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
[quoted text clipped - 27 lines]
>> > >individually and setting it up? I am using Excel 2003. Has anybody
>> > >encountered this problem?

Reply to this Message

Bob Arnett - 12 May 2008 16:30 GMT

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.

> See Ron de Bruin's site for where to find the 2007 commands.
>
[quoted text clipped - 34 lines]
> >> > >individually and setting it up? I am using Excel 2003. Has anybody
> >> > >encountered this problem?

Reply to this Message