Multiple print areas

[grahammal]

The below piece of VB prints out a print area on my worksheet OK where
the print area varies in length.

Sub Button4_Click()
cc = 26
For r = 26 To 150
If Sheets("Moves Request Form").Range("AF" & r) = "CC Reference Number
:-" Then cc = r
Next r
Sheet1.PageSetup.PrintArea = "B3:CP" & cc 'Sets the Print area
ActiveSheet.PrintOut 'Prints the Print area
Sheet1.PageSetup.PrintArea = "" 'Clears the Print area
End Sub

There is always a piece of data in the middle somewhere that I dont
need to appear on the printout.
Is there any way I can include two different print areas in the above
VB?

 

[dmexcel]

Could you hide what you don't need printed??

 

[grahammal]

Unfortunately I can't hide the unwanted printout area as this contains
options that users need to be able to select different user forms.
Their personnal details appear at the top which I need. The next part
is the options that are not required on the printout. Underneath the
options is the user forms that I do need. Hence two different print
areas.

 

[broro183]

Hi,

Youmaybe able to hide the unwanted rows in the middle for the print ou
& then immediately reshowing them using an adapted version of

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "One To Hide Rows On" Then
Rows("27:35").Hidden = True
Else
End If
End Sub

(entered in the "thisworkbook" code sheet) & then to unhide the
immediately after the printout replace the line
"ActiveSheet.PrintOut 'Prints the Print area"
in your code with
"ActiveSheet.PrintOut 'Prints the Print area
Rows("27:35").Hidden = False"
to return the layout to normal for use again.

Another option is to create a new sheet, set print area (I've left thi
for you), print from there & then delete the temp sheet using cod
similar to below:

Sheets.Add.Name = "Temp Printing Sheet"
Worksheets("Original Sheet").Range("A8:I19,A22:I33").Copy _
Worksheets("Temp Printing Sheet").Range("a1")
'set the print area on temp sheet
Worksheets("Temp Printing Sheet").PageSetup.PrintArea = "?"
Worksheets("Temp Printing Sheet").PrintOut 'Prints the Print area
Application.DisplayAlerts = False
Worksheets("Temp Printing Sheet").Delete
Application.DisplayAlerts = True

NB: The 2 ranges on the second line & the can be changed as needed.

One issue I can think of with the temp sheet method is upsetting a
headers/footers you may have set up on the printed sheet.

Hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Ron de Bruin]

Hi grahammal

Look here for examples
http://www.rondebruin.nl/print.htm