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

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

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

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" wrote in message
...
... without having to change the header on each worksheet individually.

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

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" wrote in message
...
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" wrote in message
...
... without having to change the header on each worksheet individually.

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

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" wrote in message
news:Paul.Sheppard.1tkjqd_1123711503.5708@excelfor um-nospam.com...

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

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" wrote in message
news:Paul.Sheppard.1tkjqd_1123711503.5708@excelfor um-nospam.com...

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

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

I found this to be EXACTLY what I needed to create a macro that can be run
over and over to change a page footer. The document comes in monthly from
outside and this needs to be changed every month.

Thank you!

"Earl Kiosterud" wrote:

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" wrote in message
...
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" wrote in message
...
... without having to change the header on each worksheet individually.