Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
uniform headers on Excel worksheets | Setting up and Configuration of Excel | |||
How do Print out a uniform spreadsheet i.e. all the same size A3 | Excel Discussion (Misc queries) | |||
how can i make a uniform timetable? | Excel Discussion (Misc queries) | |||
Concatenate and make uniform | Excel Worksheet Functions | |||
Concatenate and make uniform | Excel Worksheet Functions |