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

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

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