Multiple print areas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

grahammal - 28 Feb 2006 09:29 GMT

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?

Signature

grahammal

Reply to this Message

dmexcel - 28 Feb 2006 09:32 GMT

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

Reply to this Message

grahammal - 28 Feb 2006 09:43 GMT

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.

Signature

grahammal

Reply to this Message

broro183 - 28 Feb 2006 10:58 GMT

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...

grahammal Wrote:

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

Reply to this Message

Ron de Bruin - 28 Feb 2006 15:25 GMT

Hi grahammal

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

Signature

Regards Ron de Bruin
http://www.rondebruin.nl

> The below piece of VB prints out a print area on my worksheet OK where
> the print area varies in length.
[quoted text clipped - 14 lines]
> Is there any way I can include two different print areas in the above
> VB?

Reply to this Message