Select cells in 2 sheets to print in one instance

[Corey ....]

I am using the below section of code to prompt the user for a printer, but i
have 1 page to print out in 1 sheet, then a couple of pages to print out in
another sheet.
How can i set all pages to PrintOut instead of using the
ActiveSheet.PrintOut?

If i select a PDFPrinter, i get 2 pdf files because i added the 2nd sheet to
be printed, so i want something like:
Sheet1.Range("A130").Select & sheet2.Range("A160").Select
Activecells.PrintOut ?



Dim bOK As String
bOK = Application.Dialogs(xlDialogPrinterSetup).Show 'choose the
printer
If bOK = False Then Exit Sub
If bOK = True Then
On Error Resume Next
ActiveSheet.PrintOut


Corey....

 

[dan dungan]

Hi Corey,

I found this in the newsgroup from July 2002

Dan
______________________________________
From: "Craig" <[email protected]>
Date: Tue, 9 Jul 2002 19:32:41 -0700
Local: Tues, Jul 9 2002 6:32 pm
Subject: Printing multiple sheets in an array!

Hello again!!!

If I have a group of sheets to print, I've found that printing
multiple
sheets in an array is much faster that printing one sheet at a time,
especially with my office computer.
My question is:
If I have 4 sheets "Data1, Data2, Data3, Data4" and Data1 & Data4 will
always print, but I only want Data 2 to print if range A1's value =??
&
Data3 to print if range A2's value = ???, can this be done?
Is there a better way to print to a network printer quicker??

Sheets(Array("Data1", "Data2", "Data3", "Data4")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Thanks in advance!!!!
Craig


Tom Ogilvy
Newsgroups: microsoft.public.excel.programming
From: "Tom Ogilvy" <[email protected]>
Date: Wed, 10 Jul 2002 23:30:55 -0400
Local: Wed, Jul 10 2002 7:30 pm
Subject: Re: Printing multiple sheets in an array!

This doesn't explicitely select the sheets (but excel selects them,
the
reverts back to the original sheet all on its own because it
inherently
operates on grouped sheets).

Sub Tester3()
Dim varr As Variant
With Worksheets("Sheet1")
If .Range("A1") = "A" And .Range("A2") = "A" Then
varr = Array("Data1", "Data2", "Data3", "Data4")
ElseIf .Range("A1") = "A" Then
varr = Array("Data1", "Data2", "Data4")
ElseIf .Range("A2") = "A" Then
varr = Array("Data1", "Data3", "Data4")
Else
varr = Array("Data1", "Data4")
End If
End With
Sheets(varr).PrintPreview
End Sub

Regards,
Tom Ogilvy

 

[Corey ....]

Thanks,
Looks like i can adapt that code to suit.

I only need to now workout how to establish how many pages in the 2nd sheet
are required to be printed.

Corey....

 

[Corey ....]

Solved that by using the WorkSheet_Activate event to check if data existed
in a cell to set the print page setup range.

Private Sub Worksheet_Activate()
' Set up for all 4 pages
If Range("A67").Value <> "" Then ActiveSheet.PageSetup.PrintArea =
"$A$1:$Q$81"
' Set up for 3 pages
If Range("A67").Value = "" And Range("A47").Value <> "" Then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$61"
' Set up for 3(Minimun Required) pages
If Range("A67").Value = "" And Range("A47").Value = "" Then
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$41"
End Sub


Corey....

 

[dan dungan]

All right!

Dan