Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print Ranges based on conditions | Excel Discussion (Misc queries) | |||
How to print noncontiguous ranges? | Excel Discussion (Misc queries) | |||
Print area composed by more ranges | Excel Discussion (Misc queries) | |||
Print Area ranges print on separate pages? | Excel Discussion (Misc queries) | |||
Variable Print Ranges | Excel Discussion (Misc queries) |