Print Ranges

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

Sorry, forgot to enter the subject,

Hi,
I'm using a Combobox from the Forms toolbar to select print ranges via a
macro, the link cell for the Combobox being named "mon". What I have at the
moment is:-

If Range("mon").Value = 1 Then ActiveSheet.PageSetup.PrintArea =
"$B$1:$AC$54"
If Range("mon").Value = 2 Then ActiveSheet.PageSetup.PrintArea =
"$AD$1:$BE$54"
If Range("mon").Value = 3 Then ActiveSheet.PageSetup.PrintArea =
"$BF$1:$CG$54"

and so on.
I want to do something like (I know this is wrong because it doesn't work!)

ActiveSheet.PageSetup.PrintArea = Range([Whatever])

so that I can use a VLOOKUP using "mon" as the first argument in the
worksheet to put the required range in "Whatever" and use just one line of
code instead of 15 as I have at the moment.

I'm sure it can be done but I cant work out how. Can anyone please advise
me?
Hope I'm making sense,
Many thanks,
Regards,
Alan.

XL2002
WIN XP Professional
 
Assume you have your print area addresses in the range A11:A25 on Sheet3

Dim sStr as String, sName as String
sStr = Worksheets("Sheet3").Range("A11:A25") _
.Offset(Range("Mon").Value-1,0).Value
sName = "'" & Activesheet.Name & "'!"
Activesheet.PageSetup.PrintArea = sName & sStr
 
Name your print ranges "page1", "page2", etc. then use this code:

Sub PrntRng()
ActiveSheet.PageSetup.PrintArea = "page" & Range("mon").Value
End Sub
 
Thats Great!
Thanks Tom and Bradley, much appreciated,
Regards,
Alan.

Bradley Dawson said:
Name your print ranges "page1", "page2", etc. then use this code:

Sub PrntRng()
ActiveSheet.PageSetup.PrintArea = "page" & Range("mon").Value
End Sub

Alan said:
Sorry, forgot to enter the subject,

Hi,
I'm using a Combobox from the Forms toolbar to select print ranges
Click to expand...
via
a
macro, the link cell for the Combobox being named "mon". What I have at the
moment is:-

If Range("mon").Value = 1 Then ActiveSheet.PageSetup.PrintArea =
"$B$1:$AC$54"
If Range("mon").Value = 2 Then ActiveSheet.PageSetup.PrintArea =
"$AD$1:$BE$54"
If Range("mon").Value = 3 Then ActiveSheet.PageSetup.PrintArea =
"$BF$1:$CG$54"

and so on.
I want to do something like (I know this is wrong because it doesn't work!)

ActiveSheet.PageSetup.PrintArea = Range([Whatever])

so that I can use a VLOOKUP using "mon" as the first argument in the
worksheet to put the required range in "Whatever" and use just one line of
code instead of 15 as I have at the moment.

I'm sure it can be done but I cant work out how. Can anyone please advise
me?
Hope I'm making sense,
Many thanks,
Regards,
Alan.

XL2002
WIN XP Professional
Click to expand...
Click to expand...
 
Back
Top