Print Ranges

[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

 

[Tom Ogilvy]

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

 

[Bradley Dawson]

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

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

 

[Alan]

Thats Great!
Thanks Tom and Bradley, much appreciated,
Regards,
Alan.

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

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

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