adding uniform footers

[Jamie Martin]

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

 

[Robin Hammond]

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