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.

--
Regards,
Tom Ogilvy

wrote in message
...
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!!
-----Original Message-----
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

--
Regards,
Tom Ogilvy

"kristie" wrote in message
...
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!
-----Original Message-----
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



"Kristie" wrote in message
...
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


.