Setting Print Area in a Macro - PLEASE HELP!

[smugtim]

Please could somebody help me.

I need to set the print area in a macro. The range that I want to set the print area to is returned to a cell.

For example:

Cell B5 contains the value: $B$1:$O$50.
In the macro I have the line: ActiveSheet.PageSetup.PrintArea = ???

What do I put in place of the ??? above, to retrieve the value from B5?

Thank you!

TIM[/b]

 

[just_jon]

Welcome to MrExcel - try [B5].Value

 

[Norie]

Does this work?

ActiveSheet.PageSetup.PrintArea = Range("B5").Value

 

[Von Pookie]

Since the addresses are in the cell, you should be able to use:

ActiveSheet.PageSetup.PrintArea = Range("B5").Value

 

[smugtim]

Thank you so much for your quick reply. I knew there should be a very simple answer!

I couldn't believe that VB wouldn't allow a reference to a cell containing a range rather than the range itself. But couldn't find any information on this in Help.

Cheers!

TIM

 

[Von Pookie]

Yeah. For the Print Range, you can't actually refer to it as a range--you have to enter the addresses. So if you did not have the range written in a cell, you would have to use

ActiveSheet.PageSetup.PrintArea = Range("B1:O50").Address

so that it would return $B$1:$O$50 as a string.

It's goofy