Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select the print area
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select the print area
First, Try the macro recorder and if you still need help, come back.
-- Don Guillett SalesAid Software Granite Shoals, TX "Gavin" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select the print area
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 -- Regards, Tom Ogilvy Gavin wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select the print area
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 -----Original Message----- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select the print area
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. -----Original Message----- 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 -- Regards, Tom Ogilvy Gavin wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select the print area
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 "Gavin" wrote in message ... 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. -----Original Message----- 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 -- Regards, Tom Ogilvy Gavin wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel automatically select print area on non-blank cells? | Excel Discussion (Misc queries) | |||
setting the print area from a macro | Excel Worksheet Functions | |||
How to end/up end/across to select print area? | Excel Discussion (Misc queries) | |||
Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
Create a print macro that would automatically select print area? | Excel Worksheet Functions |