Print Ranges
 

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

Print Ranges
 

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

--
Regards,
Tom Ogilvy


Alan wrote in message
...
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

Print Ranges
 

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

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

"Alan" wrote in message
...
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

Print Ranges
 

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

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

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

"Alan" wrote in message
...
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