Changing footer programmatically in multiple sheets all at once

[alan674]

Because of the slow execution of .PageSetup property with specific printers,
especially when done on multiple sheets, because of cumulative time, I would
like to programmatically cahnge that property all at once on all sheets.
As that operation is possible manually if multiple sheets are selected, I
tried to register the macro and it gave following lines:

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet1 (2)", "Sheet2 (2)")).Select
Sheets("Sheet1").Activate
With ActiveSheet.PageSetup
.LeftFooter = "&12Test 3ème test" & Chr(10) & "C O N F I D E N T I A
L"
End With

Unfortunately, when replayed, the macro only modify the first sheet of the
array.

how to do that programmatically ?

Thanks in advance for your help

 

[keepITcool]

Alan,

macrocommands "old style" to the rescue:
(get a copy of macrofun.hlp from microsoft if you need a reference)

xl4 macro's are LOTS faster for setting multiple print settings.
john green posted a very usefull function too:
http://google.com/groups?selm=VA.00000b2f.0028c7e5@mara9


Worksheets.Select
Application.ExecuteExcel4Macro
("PAGE.SETUP(,""&lTextLeft&cTextCenter&rTextright"")")
ActiveSheet.Select True

or
Dim s$
s = "&L&12Test 3ème test" & vbLf & "C O N F I D E N T I A L"
Worksheets.Select
Application.ExecuteExcel4Macro _
Replace("PAGE.SETUP(,""|foot|"")", "|foot|", s)
ActiveSheet.Select True
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


alan674 wrote :

 

[Dr. Stephan Kassanke]

Because of the slow execution of .PageSetup property with specific
printers,
especially when done on multiple sheets, because of cumulative time, I
would
like to programmatically cahnge that property all at once on all sheets.
As that operation is possible manually if multiple sheets are selected, I
tried to register the macro and it gave following lines:

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet1 (2)", "Sheet2
(2)")).Select
Sheets("Sheet1").Activate
With ActiveSheet.PageSetup
.LeftFooter = "&12Test 3ème test" & Chr(10) & "C O N F I D E N T I
A
L"
End With

Unfortunately, when replayed, the macro only modify the first sheet of the
array.

how to do that programmatically ?

Thanks in advance for your help

Hi Alan(?)

you need a loop to access all members of the Worksheets collection

for each mySheet in Worksheets
mySheet.leftfooter = "Whatever..."
next

cheers,
Stephan