Changing footer programmatically in multiple sheets all at once
 

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

Changing footer programmatically in multiple sheets all at once
 

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.000...28c7e5%40mara9


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 :

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

Changing footer programmatically in multiple sheets all at once
 

"alan674" schrieb im Newsbeitrag
...
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