I need to set multiple worksheets with the same Page
setup. e.g. .25 borders, 1 page wide, 1 page tall, etc.
BUT looping through the sheets significantly slows down
the performace of my code. Is there a better way to
accomplish this?

Hi:

See this post by Tom Ogilvy, with a reference to John Green.

http://google.com/groups?selm=e2mdJn...FTNGP12&rnum=4

Regards,

Vasant.


"MDC" wrote in message
...
I need to set multiple worksheets with the same Page
setup. e.g. .25 borders, 1 page wide, 1 page tall, etc.
BUT looping through the sheets significantly slows down
the performace of my code. Is there a better way to
accomplish this?

A manual trick I heard about to get this done easily is
the following "recepy" :

1. Set up the page-setup as you need it on one of the tab
sheets
2. Select the tab sheet with the desired page-setup
3. With the control key pressed, select each of the other
tab sheets on which you want to apply the page-setup
4. Go to the page setup selection on the file menu
5. Click "OK" without changing anything

Result of these 5 steps : the page-setup defined in step
1 is copied over to all of the selected sheets.

I don''t know how to do a VBA routine without looping,
but this trick works and it works very fast ...
-----Original Message-----
Hi:

See this post by Tom Ogilvy, with a reference to John
Green.

http://google.com/groups?selm=e2mdJn40CHA.868%
40TK2MSFTNGP12&rnum=4

Regards,

Vasant.


"MDC" wrote in
message
...
I need to set multiple worksheets with the same Page
setup. e.g. .25 borders, 1 page wide, 1 page tall,
etc.
BUT looping through the sheets significantly slows down
the performace of my code. Is there a better way to
accomplish this?


.

KeepItCool posted a VBA approach that uses that method by simulating
keystokes:


This is replicating the fastest manual way...

sheets(array("sheet2","sheet3","sheet4")).select
sheets("sheet3").activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show



keepITcool

--
Regards,
Tom Ogilvy


philip wrote in message
...
A manual trick I heard about to get this done easily is
the following "recepy" :

1. Set up the page-setup as you need it on one of the tab
sheets
2. Select the tab sheet with the desired page-setup
3. With the control key pressed, select each of the other
tab sheets on which you want to apply the page-setup
4. Go to the page setup selection on the file menu
5. Click "OK" without changing anything

Result of these 5 steps : the page-setup defined in step
1 is copied over to all of the selected sheets.

I don''t know how to do a VBA routine without looping,
but this trick works and it works very fast ...
-----Original Message-----
Hi:

See this post by Tom Ogilvy, with a reference to John
Green.

http://google.com/groups?selm=e2mdJn40CHA.868%
40TK2MSFTNGP12&rnum=4

Regards,

Vasant.


"MDC" wrote in
message
...
I need to set multiple worksheets with the same Page
setup. e.g. .25 borders, 1 page wide, 1 page tall,
etc.
BUT looping through the sheets significantly slows down
the performace of my code. Is there a better way to
accomplish this?


.

Does anyone know how select all sheets in a workbook when
the number & names of sheets are not known?

I would like to have a macro that would put header/footer
on every worksheet in a workbook without having to loop
through each sheet - when I receive workbooks with 12-15
worksheets, looping through each sheet is a little slow.
Below is the code I'm currently using.

Sub AddHeader_Footer()
Dim ws As Object

For Each ws In Sheets
With ActiveSheet.PageSetup
.RightHeader = "Page &P of &N"
.LeftFooter = "&7&Z&F" & Chr(10) & "&A"
.RightFooter = "Printed: &D @ &T"
End With
Next ws
End Sub

Thanks, Kathryn

-----Original Message-----
KeepItCool posted a VBA approach that uses that method
by simulating
keystokes:


This is replicating the fastest manual way...

sheets(array("sheet2","sheet3","sheet4")).selec t
sheets("sheet3").activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show



keepITcool

--
Regards,
Tom Ogilvy


philip wrote in
message
...
A manual trick I heard about to get this done easily is
the following "recepy" :

1. Set up the page-setup as you need it on one of the
tab
sheets
2. Select the tab sheet with the desired page-setup
3. With the control key pressed, select each of the
other
tab sheets on which you want to apply the page-setup
4. Go to the page setup selection on the file menu
5. Click "OK" without changing anything

Result of these 5 steps : the page-setup defined in
step
1 is copied over to all of the selected sheets.

I don''t know how to do a VBA routine without looping,
but this trick works and it works very fast ...
-----Original Message-----
Hi:

See this post by Tom Ogilvy, with a reference to John
Green.

http://google.com/groups?selm=e2mdJn40CHA.868%
40TK2MSFTNGP12&rnum=4

Regards,

Vasant.


"MDC" wrote in
message
...
I need to set multiple worksheets with the same Page
setup. e.g. .25 borders, 1 page wide, 1 page tall,
etc.
BUT looping through the sheets significantly slows
down
the performace of my code. Is there a better way to
accomplish this?


.



.

Try using the Workbook_BeforePrint(Cancel As Boolean) event

HTH
-Merk


11/25/03 12:42PM
Does anyone know how select all sheets in a workbook when
the number & names of sheets are not known?

I would like to have a macro that would put header/footer
on every worksheet in a workbook without having to loop
through each sheet - when I receive workbooks with 12-15
worksheets, looping through each sheet is a little slow.
Below is the code I'm currently using.

Sub AddHeader_Footer()
Dim ws As Object

For Each ws In Sheets
With ActiveSheet.PageSetup
.RightHeader = "Page &P of &N"
.LeftFooter = "&7&Z&F" & Chr(10) & "&A"
.RightFooter = "Printed: &D @ &T"
End With
Next ws
End Sub

Thanks, Kathryn

-----Original Message-----
KeepItCool posted a VBA approach that uses that method
by simulating
keystokes:


This is replicating the fastest manual way...

sheets(array("sheet2","sheet3","sheet4")).selec t
sheets("sheet3").activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show



keepITcool

--
Regards,
Tom Ogilvy


philip wrote in
message
...
A manual trick I heard about to get this done easily is
the following "recepy" :

1. Set up the page-setup as you need it on one of the
tab
sheets
2. Select the tab sheet with the desired page-setup
3. With the control key pressed, select each of the
other
tab sheets on which you want to apply the page-setup
4. Go to the page setup selection on the file menu
5. Click "OK" without changing anything

Result of these 5 steps : the page-setup defined in
step
1 is copied over to all of the selected sheets.

I don''t know how to do a VBA routine without looping,
but this trick works and it works very fast ...
-----Original Message-----
Hi:

See this post by Tom Ogilvy, with a reference to John
Green.

http://google.com/groups?selm=e2mdJn40CHA.868%
40TK2MSFTNGP12&rnum=4

Regards,

Vasant.


"MDC" wrote in
message
...
I need to set multiple worksheets with the same Page
setup. e.g. .25 borders, 1 page wide, 1 page tall,
etc.
BUT looping through the sheets significantly slows
down
the performace of my code. Is there a better way to
accomplish this?


.



.