How do I change headers for all worksheets in a workbook?

[frankfine]

.... without having to change the header on each worksheet individually.

[Paul Sheppard]

Try selecting all sheets and then changing the headers, all should change at the same time

Paul

[Earl Kiosterud]

Frank,

I think you're referring to the custom header, in File - Page Setup. If all
the page setup parameters (margins, rows to repeat, etc.) are to be the
same for all sheets. Set up one sheet (File - Page setup). Then select the
remainder of the sheets and use Edit - Repeat Page setup. If they're
different, and you want only to put in a custom header, use this macro:

Sub SetPageHeading()

Dim sht As Worksheet

For Each sht In Worksheets
With sht.PageSetup
.LeftHeader = ""
.CenterHeader = "My Page Heading, &""Arial,Italic""per se"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.PrintErrors = xlPrintErrorsDisplayed
End With
Next sht
End Sub

Substitute any lines as needed. You can record a macro as you set up and
format a custom header, then use the needed lines in this macro.
--
Earl Kiosterud
www.smokeylake.com

"frankfine" <[email protected]> wrote in message
news:[email protected]...
> ... without having to change the header on each worksheet individually.

[frankfine]

Is there a command for selecting all worksheets?
Frank

"Paul Sheppard" wrote:

>
> Try selecting all sheets and then changing the headers, all should
> change at the same time
>
> Paul
>
>
> --
> Paul Sheppard
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> View this thread: http://www.excelforum.com/showthread...hreadid=394672
>
>

[Earl Kiosterud]

Frank,

This is about the dumbest reply that's been seen for a while. Too much
trouble. Use Paul's suggestion instead (unless there are other Page Setup
parameters that will be different -- in that case use my macro solution). I
was thinking you can't do page setup on more than one sheet. You can. You
can't with Sheet protection. This isn't about sheet protection. They'll
let anyone respond in these groups, won't they? Sorry.
--
Earl Kiosterud
www.smokeylake.com

"Earl Kiosterud" <[email protected]> wrote in message
news:[email protected]...
> Frank,
>
> I think you're referring to the custom header, in File - Page Setup. If
> all the page setup parameters (margins, rows to repeat, etc.) are to be
> the same for all sheets. Set up one sheet (File - Page setup). Then
> select the remainder of the sheets and use Edit - Repeat Page setup. If
> they're different, and you want only to put in a custom header, use this
> macro:
>
> Sub SetPageHeading()
>
> Dim sht As Worksheet
>
> For Each sht In Worksheets
> With sht.PageSetup
> .LeftHeader = ""
> .CenterHeader = "My Page Heading, &""Arial,Italic""per se"
> .RightHeader = ""
> .LeftFooter = ""
> .CenterFooter = ""
> .RightFooter = ""
> .PrintErrors = xlPrintErrorsDisplayed
> End With
> Next sht
> End Sub
>
> Substitute any lines as needed. You can record a macro as you set up and
> format a custom header, then use the needed lines in this macro.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "frankfine" <[email protected]> wrote in message
> news:[email protected]...
>> ... without having to change the header on each worksheet individually.
>
>

[Paul Sheppard]

[QUOTE=frankfine]Is there a command for selecting all worksheets?
Frank

Frank

Click on the first sheet and if you want to select all sheets in the workbook hold down the shift key and click on the last worksheet, this will select them all

If you only want some of the worksheets and they are consecutive then the same as previous but click on the last sheet you want to select

Or if you want a number of non consecutive sheets click on the first one and then holding down the CTRL key click on the remaining sheets you want to select

Hope this helps

Paul

[David McRitchie]

Right click on any worksheet tab, then use Select All

don't forget to ungroup the worksheets when finished, by
right clicking on a worksheet tab and ungroup -- to prevent
making changes afterwards to all your grouped sheets
that would damage your workbook.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Paul Sheppard" <[email protected]> wrote in message
news:[email protected]...
>
> frankfine Wrote:
> > Is there a command for selecting all worksheets?
> > Frank
> >
> > Frank
> >
> > Click on the first sheet and if you want to select all sheets in the
> > workbook hold down the shift key and click on the last worksheet, this
> > will select them all
> >
> > If you only want some of the worksheets and they are consecutive then
> > the same as previous but click on the last sheet you want to select
> >
> > Or if you want a number of non consecutive sheets click on the first
> > one and then holding down the CTRL key click on the remaining sheets
> > you want to select
> >
> > Hope this helps
> >
> > Paul
>
>
> --
> Paul Sheppard
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> View this thread: http://www.excelforum.com/showthread...hreadid=394672
>

[frankfine]

Thank you Paul and Richie,
You led me in the right direction, that is right clicking on the the sheet
tab. There I found "Select all sheets". It works once and seems to turn
itself off once you change sheets or actions.
The difference in procedures probably is due to our using different
versions. I use Office Excel 2003.
Frank

"David McRitchie" wrote:

> Right click on any worksheet tab, then use Select All
>
> don't forget to ungroup the worksheets when finished, by
> right clicking on a worksheet tab and ungroup -- to prevent
> making changes afterwards to all your grouped sheets
> that would damage your workbook.
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Paul Sheppard" <[email protected]> wrote in message
> news:[email protected]...
> >
> > frankfine Wrote:
> > > Is there a command for selecting all worksheets?
> > > Frank
> > >
> > > Frank
> > >
> > > Click on the first sheet and if you want to select all sheets in the
> > > workbook hold down the shift key and click on the last worksheet, this
> > > will select them all
> > >
> > > If you only want some of the worksheets and they are consecutive then
> > > the same as previous but click on the last sheet you want to select
> > >
> > > Or if you want a number of non consecutive sheets click on the first
> > > one and then holding down the CTRL key click on the remaining sheets
> > > you want to select
> > >
> > > Hope this helps
> > >
> > > Paul
> >
> >
> > --
> > Paul Sheppard
> > ------------------------------------------------------------------------
> > Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> > View this thread: http://www.excelforum.com/showthread...hreadid=394672
> >
>
>
>