Macro to select the print area

[Gavin]

I have a table to which the print area may change
significantly, and wish to write a macro to defines the
print area.
I have a cell in the spreadsheet, $A$1, whos text defines
the print area (this is done using the CELL("address", )
worksheet function). On the page setup sheet, I can type
into the print area box =INDIRECT($A$1) and the print area
is set correctly.

I wish to write a macro that enters =INDIRECT($A$1) as the
print area. Visual Basic does not recognize the INDIRECT
formula from Excell.

Does anyone know how to do this? Or know another way to
write a macro to define the print area without having to
enter the cell range?

Regards,

Gav

 

[Don Guillett]

First, Try the macro recorder and if you still need help, come back.

 

[Tom Ogilvy]

If you put it in one time (manually for instance), then you shouldn't have
to worry about it - just put the correct value in A1.

Sub SetPrintArea()
ActiveWorkbook.Names.Add Name:= _
"Sheet1!Print_Area", _
RefersTo:="=INDIRECT(Sheet1!A1)"
End Sub

 

[Kyle]

I would do it by defining the top left cell as a range
name, eg Start - then the bottom right cell of your data
range as End. In your Macro, use the command F5 or Goto
(Start:End). This way you always get the start & end
selected for your print area. This is fine for just one
block of data - if you need to scroll through periods of
the year depending on what month you are viewing - eg the
12 months from Mar02 - Mar03 if I set my first month as
March02, then you can do nested IF statements in the GOTO
statement to select the top & bottom of the defined range.
It means there's a hell of a lot more defined names though
I haven't found a better way to do this yet. Suggestions?
Kyle

 

[Gavin]

This was my initial intention, but the formula written in
the print area box is not saved.
After you click ok, then go back to the print area box,
the formula entered is mereley an absolute reference -
being the text value it read from cell $A$1. The formula I
entered =INDIRECT($A$1) is not there, only the value that
was returned from from that formula is written in the box.
Consequently this action only works the once, which is why
I thought I'd get around it by writing a macro to enter
the formula each time, but ran into trouble.