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

(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

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