Apply pagesetup to workbook?

[sam99car]

Hello,

My question is: I have a macro that "cycles" each worksheet and formats the data to our needs-row and column size, change font and size, change alignment, change "pagesetup" for the printer. All works well and quick, except the "pagesetup" for the printer.

Can I apply the following code to a workbook instead of each worksheet?

Thanks in advance. Have a great Thanksgiving weekend (Canada)

-sam
With ActiveSheet.PageSetup
.PrintTitleRows = "$6:$6"
.PrintTitleColumns = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&""Helv,Regular""&9&P of &N"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

 

[stevebausch]

I can't claim any intelligence for this technique; another MrExcel member mentioned it one time, and I was stunned with it's simplicity.

Hold down the Control key, and select all desired sheet tabs.

Go to Page Setup, make your choices.

The key to doing this in a macro is to record a macro while doing the above Ctrl-select all tabs. Look at the code, you will find:


Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet2").Activate
With ActiveSheet.PageSetup

as the code that sets the scene for the PageSetup applying to all sheets.

 

[sam99car]

Thanks Steve,

I will give it a try.

I find it silly that for each workbook, I must apply the "With ActiveSheet.PageSetup" to each worksheet to get the required output = SLOW!!!

Again, thanks Steve.

-sam.

 

[lenze]

Try something like this

Sub Format()
Dim ws As Worksheet

For Each ws In Sheets
ws.Activate
With ActiveSheet.PageSet
'Your setup lines
End With
Next
End Sub

You might want to place it in an Event procedure(Before Print?) instead of a regular module.