Page Setup for multiple sheets

[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?

 

[Vasant Nanavati]

Hi:

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

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

Regards,

Vasant.

 

[philip]

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 ...

 

[Tom Ogilvy]

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

 

[Kathryn]

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

 

[Merkling, Steven]

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