Discussion:
Page Setup for multiple sheets
(too old to reply)
MDC
22 years ago
Permalink
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?
unknown
22 years ago
Permalink
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.
Post by MDC
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?
philip
22 years ago
Permalink
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-----
See this post by Tom Ogilvy, with a reference to John
Green.
http://google.com/groups?selm=e2mdJn40CHA.868%
40TK2MSFTNGP12&rnum=4
Regards,
Vasant.
Post by MDC
I need to set multiple worksheets with the same Page
setup. e.g. .25 borders, 1 page wide, 1 page tall,
etc.
Post by MDC
BUT looping through the sheets significantly slows down
the performace of my code. Is there a better way to
accomplish this?
.
Tom Ogilvy
22 years ago
Permalink
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
...
Kathryn
22 years ago
Permalink
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
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
Post by philip
A manual trick I heard about to get this done easily is
1. Set up the page-setup as you need it on one of the
tab
Post by philip
sheets
2. Select the tab sheet with the desired page-setup
3. With the control key pressed, select each of the
other
Post by philip
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
...
down
Post by philip
Post by MDC
the performace of my code. Is there a better way to
accomplish this?
.
.
Merkling, Steven
22 years ago
Permalink
Try using the Workbook_BeforePrint(Cancel As Boolean) event

HTH
-Merk
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
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
Post by philip
A manual trick I heard about to get this done easily is
1. Set up the page-setup as you need it on one of the
tab
Post by philip
sheets
2. Select the tab sheet with the desired page-setup
3. With the control key pressed, select each of the
other
Post by philip
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
...
down
Post by philip
Post by MDC
the performace of my code. Is there a better way to
accomplish this?
.
.
Loading...