Macro to select the print area

  • Thread starter Thread starter Gavin
  • Start date Start date
G

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
 
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
 
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
 
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.
 
I don't know what you are doing, but I had no problem entering a formula for
the refers to portion of the defined name

Sheet1!Print_Area

and changed the Print_Area by changing the range specified in cell A1 of
Sheet1.

I entered the formula by using Insert=>Name=>Define.

Name: Sheet1!Print_Area
Refers to: =Indirect(Sheet1!$A$1)


Regards,
Tom Ogilvy
 
Back
Top