Newbie here: I have a question about a print macro I am trying to write...

[Kyaba]

Essentially what I am trying to do is create a print macro that will
print selected sheets from a massive group of worksheets. I have
created the following macro which works however, this requires me to
name print areas for each specific category, which has a number of
sheets associated to it. I am sure there is an easier way to go about
this rather than Naming print areas for thousands of sheets.



Sub Macro37()
Application.Goto Reference:="Consolidated_2006_Actual"
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Application.Goto Reference:="Consolidated_2006_Budget"
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Application.Goto Reference:="Consolidated_2005_Actual"
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Application.Goto Reference:="Consolidated_TTM"
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Application.Goto Reference:="Consolidated_Rolling_Actuals"
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Application.Goto Reference:="Consolidated_NOI_Valuations"
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Application.Goto Reference:="Print_Directory"
End Sub


Any help would be appreciated!! Thanks!!

KYABA

 

[Ron de Bruin]

Hi

If you want to print all data on each sheet why set the printarea ?
Clear the printarea and you can use a array with sheet names

Sheets(Array("Sheet1", "Sheet3")).PrintOut
'all sheets in the array

 

[Kyaba]

Hi Ron,

Thanks for your response.

With regards to my sheet, I am trying to limit the printing to a
certain section of the worksheet. For example category1 will have the
print areas of A3:R66, while category2 will have a print area of A68 to
R134. This is also how it is set up on 6 other worksheets.

So what I would like to do is set up a macro that when run would print
all the sheets corresponding to category1, thus printing out 6 sheets
from the 6 different tabs. The problem I was facing originally was
naming the print areas for all 66 categories and then again for all 6
sheets.

 

[Ron de Bruin]

Why not loop through the all sheets and print the range

Sub test()
Dim sh As Worksheet
For Each sh In Sheets(Array("Sheet1", "Sheet3"))
sh.Range("A3:R66").PrintOut
Next sh
End Sub

 

[Kyaba]

Thanks for the Help Ron. I just have another quick question.

ActiveWindow.SelectedSheets.PrintOut From:=2, To:=2, Copies:=1, _
ActivePrinter:="\\svnydc03\Canon iR C3220 PCL5c on Ne02:",
Collate:=True
Sheets("2006 Actual").Select

what syntax can I use to make the activeprinter non specific so that
any user can print to whatever default printer they have available?

 

[Ron de Bruin]

You can use this maybe ?

Application.Dialogs(xlDialogPrinterSetup).Show
ActiveSheet.PrintOut