adding uniform footers
 

Hi,

I am a VBA newbie and am not too good at tracing my way through the
object model tree. I want to give all the worksheets in 500 Excel files
a standard footer (but a different one if the sheet is a chart instead
of a data table). Here's what I have:

Sub StandardizeFooters()
Book As Workbook
Sheet As Worksheet
For Each Book In Workbooks
For Each Sheet In Book
With ActiveSheet.PageSetup
.LeftFooter = "&F"
.CenterFooter = "printed &D"
.RightFooter = "&P of &N"
End With
Next Sheet
Book.Close
Next Book
End Sub

Can anyone give me the correct syntax for a worksheet, as well as a way
to distinguish between sheets that are charts and those that aren't?
Thanks very much.

Jamie

adding uniform footers
 

Looks like you either need to specify more than the activesheet
e.g.
with Sheet.PageSetup ' NOT ACTIVESHEET

You should be able to cut your page setup code into this type of construct,
which loops sheet and charts.

Option Explicit

Sub Test()
Dim W As Workbook
Dim S As Worksheet
Dim C As Chart

For Each W In Workbooks
For Each S In wbook.Worksheets
Debug.Print "Book:", wbook.Name, "Sheet:", S.Name
'with S.Pagesetup etc

Next S

For Each C In wbook.Charts
Debug.Print "Book:", wbook.Name, "Chart:", C.Name
Next C


W.Save 'this saves the changes
W.Close(False) 'this closes the file with no prompt to save changes

Next W
End Sub

Good luck,

Robin Hammond
www.enhanceddatasystems.com
"Jamie Martin" wrote in message
...
Hi,

I am a VBA newbie and am not too good at tracing my way through the
object model tree. I want to give all the worksheets in 500 Excel files
a standard footer (but a different one if the sheet is a chart instead
of a data table). Here's what I have:

Sub StandardizeFooters()
Book As Workbook
Sheet As Worksheet
For Each Book In Workbooks
For Each Sheet In Book
With ActiveSheet.PageSetup
.LeftFooter = "&F"
.CenterFooter = "printed &D"
.RightFooter = "&P of &N"
End With
Next Sheet
Book.Close
Next Book
End Sub

Can anyone give me the correct syntax for a worksheet, as well as a way
to distinguish between sheets that are charts and those that aren't?
Thanks very much.

Jamie