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

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

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

"Gord Dibben" wrote:

Select the sheet with the custom header and footer.

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

Go into FilePage SetupHeaders 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 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?

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" wrote:

Select the sheet with the custom header and footer.

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

Go into FilePage SetupHeaders 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 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?

Hope you didn't save the workbookg

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

On Wed, 13 Feb 2008 16:01:08 -0800, Teresa
wrote:

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" wrote:

Select the sheet with the custom header and footer.

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

Go into FilePage SetupHeaders 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 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?

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?

"Teresa" wrote:

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" wrote:

Select the sheet with the custom header and footer.

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

Go into FilePage SetupHeaders 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 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?

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

On Fri, 9 May 2008 13:25:20 -0700, Bob Arnett
wrote:

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?

"Teresa" wrote:

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" wrote:

Select the sheet with the custom header and footer.

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

Go into FilePage SetupHeaders 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 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?

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.

"Gord Dibben" wrote:

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

On Fri, 9 May 2008 13:25:20 -0700, Bob Arnett
wrote:

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?

"Teresa" wrote:

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" wrote:

Select the sheet with the custom header and footer.

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

Go into FilePage SetupHeaders 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 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?