| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
How do I change headers for all worksheets in a workbook?
.... without having to change the header on each worksheet individually.
|
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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. |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| In MS Excel, how do I change the column ref headers from numbers . | Excel Discussion (Misc queries) | |||
| Sheet Column Headings and Row Labels | Excel Worksheet Functions | |||
| How to protect and unprotect 30 worksheets in a file every month . | Excel Worksheet Functions | |||
| Repeating Column Headers And Blank Worksheets With Fomulas | Excel Discussion (Misc queries) | |||
| In Excel, can I copy Headers and Footers between Worksheets? | Excel Worksheet Functions | |||
Linear Mode
