MULTIPLE PRINT AREA

[Raz]

how to have multiple print area on the same worksheet and print one that
needed only?

I have a spreadsheet with 5 sections. I need to have 5 different print area.
and
according to entries in another worksheet I would need to print only one of
them.
also need to have page break (vertical) at specific locations.

First of all is this possible???

here is what I have in my worksheet.
top 5 rows are frozen (freezzing pane)
First Section: A6 to N13 (vertically for 2 pages) Need page break at A50

Second Section: Q6 to AC16 (vertically for 2 pages) Need page break at A50

and so on.
Need to print only one of four sections, according to entry on another sheet
say if A1="One" i would print First Section, if A1="Two" i would print
Second Section and so on.

 

[Ken]

Raz
I would start by creating multiple named ranges, corresponding to your
various print ranges e.g. "one", "two", "three". Then in the
Workbook_BeforePrint event code put something like:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim x As Integer
x = Range("a1").Value
Select Case x
Case 1
ActiveSheet.PageSetup.PrintArea = "one"
Case 2
ActiveSheet.PageSetup.PrintArea = "two"
Case 3
ActiveSheet.PageSetup.PrintArea = "three"
End Select
End Sub

where the value in A1 is what determines which of your multiple print
ranges is used.

Good luck.

Ken

 

[Raz]

thanks Ken,
But where do I set my print areas?
in terms of your code,
in Case 1, my print area is A6:N50
in Case 2, my print area is Q6:AC50
and so on.

and also where to put your code? in print area dialog or in macro code(right
click--view code)?

Please explain, as I am new to this.
Thanks AGain

 

[Ken]

Raz

You probably figured it out by now, but:

The print areas are really just named ranges which corrospond to
intended areas to print. Under Insert-Name-Define (Excel 2003)
highlight your ranges, and give them intuitive names.

The code goes in the Workbook_BeforePrint event code on the code sheet
uner "ThisWorkbook". Double click on the appropriate "ThisWorkbook"
object in the VBE, select Workbook from the drop down on the left,
then BeforePrint from the dropdown on the right. That is where the
code goes.

Good luck.

Ken