| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating Footers for Multiple sheets in Workbook
Hi,
I am just beginning to try and learn about VBA. I have a spreadsheet that has 79 different tabs that I use for a price book. I need to be able to automatically change the footers with a new effective date on each tab. I have the following code which works ok, but I have to run it on each tab. Is there a way to write this that will automatically change all 79 tabs by running it only once? ActiveSheet.PageSetup.RightFooter = "&I& Effective:February 20, 2006" Any help would be greatly appreciated. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Generating Footers for Multiple sheets in Workbook
(adjust to your needs).
Customize the Page Sections (Header/Footer)of a worksheet as desired. Then from this same worksheet - run this macro: Sub change_all_myPgSettings() With ActiveSheet LF = .PageSetup.LeftFooter CF = .PageSetup.CenterFooter RF = .PageSetup.RightFooter LH = .PageSetup.LeftHeader CH = .PageSetup.CenterHeader RH = .PageSetup.RightHeader End With For Each sht In Sheets sht.Select ActiveSheet.PageSetup.LeftFooter = LF ActiveSheet.PageSetup.CenterFooter = CF ActiveSheet.PageSetup.RightFooter = RF ActiveSheet.PageSetup.LeftHeader = LH ActiveSheet.PageSetup.CenterHeader = CH ActiveSheet.PageSetup.RightHeader = RH Next sht End Sub Report back, if problems. Remember to back up you r file before trying ANYTHING NEW!! "Ron" wrote: Hi, I am just beginning to try and learn about VBA. I have a spreadsheet that has 79 different tabs that I use for a price book. I need to be able to automatically change the footers with a new effective date on each tab. I have the following code which works ok, but I have to run it on each tab. Is there a way to write this that will automatically change all 79 tabs by running it only once? ActiveSheet.PageSetup.RightFooter = "&I& Effective:February 20, 2006" Any help would be greatly appreciated. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Generating Footers for Multiple sheets in Workbook
Thanks Jim, That works like a charm.
"Jim May" wrote: (adjust to your needs). Customize the Page Sections (Header/Footer)of a worksheet as desired. Then from this same worksheet - run this macro: Sub change_all_myPgSettings() With ActiveSheet LF = .PageSetup.LeftFooter CF = .PageSetup.CenterFooter RF = .PageSetup.RightFooter LH = .PageSetup.LeftHeader CH = .PageSetup.CenterHeader RH = .PageSetup.RightHeader End With For Each sht In Sheets sht.Select ActiveSheet.PageSetup.LeftFooter = LF ActiveSheet.PageSetup.CenterFooter = CF ActiveSheet.PageSetup.RightFooter = RF ActiveSheet.PageSetup.LeftHeader = LH ActiveSheet.PageSetup.CenterHeader = CH ActiveSheet.PageSetup.RightHeader = RH Next sht End Sub Report back, if problems. Remember to back up you r file before trying ANYTHING NEW!! "Ron" wrote: Hi, I am just beginning to try and learn about VBA. I have a spreadsheet that has 79 different tabs that I use for a price book. I need to be able to automatically change the footers with a new effective date on each tab. I have the following code which works ok, but I have to run it on each tab. Is there a way to write this that will automatically change all 79 tabs by running it only once? ActiveSheet.PageSetup.RightFooter = "&I& Effective:February 20, 2006" Any help would be greatly appreciated. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I duplicate headers and footers on multiple sheets? | Excel Worksheet Functions | |||
| Generating parts list from multiple sheets | New Users to Excel | |||
| Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
| how to add footers to ALL sheets in a workbook at once? | Excel Discussion (Misc queries) | |||
| Generating multiple sheets within a workbook all titled with a ca. | Excel Programming | |||
Linear Mode
