Same Footer, but different page setup options

[Kristie]

Is there any way to force all pages of a spreadsheet to
have the same Footer without affecting the other "page set
up" options? Selecting all sheets and adding the footer
changes all sheets to the same settings otherwise.
Perhaps this could be done in VB? Thank you
Kristie

 

[Rodney POWELL]

Kristie --


Sub EditPageFooter()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.PageSetup.LeftFooter = "Hello World"

Next ws

End Sub


-----

Hope it Helps,

- Rodney POWELL
Microsoft MVP - Excel

Beyond Technology
Spring, Texas USA
www.BeyondTechnology.com



Is there any way to force all pages of a spreadsheet to
have the same Footer without affecting the other "page set
up" options? Selecting all sheets and adding the footer
changes all sheets to the same settings otherwise.
Perhaps this could be done in VB? Thank you
Kristie

 

[kristie]

Is it possible for the user to be prompted to enter the
text to be included for all sheets - or can this macro
pull data from a specified field within the spreadsheet? I
tried using = Sheet1!A2 for example and got an error. I
might even want to pull the data from another file - how
can I specify the file name, sheet name, and cell within
the code instead of using quotes and entering the data in
the macro? Thank you!

 

[Tom Ogilvy]

Sub EditPageFooter()

Dim ws As Worksheet
dim rng as Range
' Mydata.xls must be open
set rng = Workbooks("MyData.xls").Worksheets("Sheet3") _
.Range("B9").Value
For Each ws In ThisWorkbook.Worksheets

ws.PageSetup.LeftFooter = rng.Value

Next ws

End Sub

 

[Guest]

When I do this I get runtime error 424 - Required object.
Am I supposed to do anything to this code (besides edit
the name of the file and select the cell I am referring
to. Are there any other edits I need to make to make this
usable. Thank you so much!!

 

[Tom Ogilvy]

Had a typo - there should be no value on the end of Range("B9")

Sub EditPageFooter()

Dim ws As Worksheet
Dim rng As Range
' Mydata.xls must be open
Set rng = Workbooks("MyData.xls").Worksheets("Sheet3") _
.Range("B9")
For Each ws In ThisWorkbook.Worksheets

ws.PageSetup.LeftFooter = rng.Value

Next ws

End Sub

worked for me.