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

On Tue, 8 Aug 2006 12:16:02 -0700, ssr5 <(E-Mail Removed)> 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 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 <(E-Mail Removed)> 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 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 <(E-Mail Removed)> 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 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

On Wed, 13 Feb 2008 16:01:08 -0800, Teresa <(E-Mail Removed)>
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 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 <(E-Mail Removed)> 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 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 <(E-Mail Removed)> 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
<(E-Mail Removed)> 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 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 <(E-Mail Removed)> 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
> <(E-Mail Removed)> 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 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 <(E-Mail Removed)> 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?
> >> >
> >> >
>
>

[Jack45]

Your answer was nothing less than brilliant! Thanks!

Originally Posted by 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

On Tue, 8 Aug 2006 12:16:02 -0700, ssr5 <(E-Mail Removed)> 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?