PDA

View Full Version : Pagesetup and set PrintArea for multiple ranges and sheets



Jason_3335
03-20-2009, 12:05 PM
Hello, I am trying to create a macro that will print only the cell ranges A1:V47 and A50:V96 for 10 different labeled worksheets, in a report with multiple users. I want to set the print settings so that I override any preferences that may be set in the workbook by other users.

I have tried this code below:

Sheets(Array("Total", "Total Ex Intercompany", "Atl", "Que", "East", "Ont", "West", _
"VFS", "Warehouse", "Intercompany", "Corp", "Area Summary")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1

This code does the job as long as the print settings are set to landscape and 1 pg wide 2 pages tall on each sheet. But if someone changes the settings on any individual sheet the code will print based on the settings.

My question is how do I work in PageSetup into this macro so that it applies the same settings to each sheet in the Array?

I have tried this:

Sheets(Array("Total", "Total Ex Intercompany", "Atl", "Que", "East", "Ont", "West", _
"VFS", "Warehouse", "Intercompany", "Corp", "Area Summary")).Select
With SelectedSheets.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 2
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1

But I get a compile error.

GTO
03-20-2009, 05:11 PM
Greetings Jason,

Try:
Sub ex()
Dim wks As Worksheet

For Each wks In Worksheets(ArrayArray("Total", "Total Ex Intercompany", "Atl", _
"Que", "East", "Ont", "West", _
"VFS", "Warehouse", "Intercompany", "Corp", _
"Area Summary"))

With wks
With .PageSetup
.PrintArea = "$A$1:$V$47,$A$50:$V$96"
.FitToPagesTall = 2
.FitToPagesWide = 1
End With
.PrintOut Copies:=1
End With
Next
End Sub

Hope this helps,

Mark