Page Setup for multiple sheets

  • Thread starter Thread starter MDC
  • Start date Start date
M

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?
 
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 ...
 
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
 
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
 
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
 
Back
Top