Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to end/up end/across to select print area?
This should be easy, but it won't work.
I have a cash analysis spreadsheet that expands downwards as it goes, and I simply want to print the latest month off in a macro that closes the month and does all sorts of other complex stuff perfectly. I have created a "rail" on the right hand edge with a "|" character to end/up to a break in that column. With macro set to "relative" I record shift/end/up from an named anchor point to select the vertical area, then try to record shift/left left left etc to select all the columns (as not all cells contain data). I print and naturally it prints perfectly, but when I run the macro it goes haywire, printing way up beyond the area selected. This is how that section of the recorded macro looks: Application.Goto Reference:="Total" (bottome edge of sheet, left hand side) Selection.End(xlUp).Select (goes to last entry) ActiveCell.Offset(0, 61).Range("A1").Select (goes to final column containing the 'rail') Selection.ClearContents (creates a break in the rail for next month) Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlUp)).Select (selects column up to break in rail at last month) ActiveCell.Offset(-120, -61).Range("A1:BJ121").Select (attempting to make the selection 3 dimensional to include all columns) ActiveCell.Activate Selection.PrintOut Copies:=1, Collate:=True (Bombs out, printing to about a month an a half further back than my topmost selection!) Can anyone tell me where I'm goimg wrong, or offer a solution? Thanks, Keith |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to end/up end/across to select print area?
Sorry... should have mentioned I'm working in Excel 2000 v.9.
"Kevryl" wrote: This should be easy, but it won't work. I have a cash analysis spreadsheet that expands downwards as it goes, and I simply want to print the latest month off in a macro that closes the month and does all sorts of other complex stuff perfectly. I have created a "rail" on the right hand edge with a "|" character to end/up to a break in that column. With macro set to "relative" I record shift/end/up from an named anchor point to select the vertical area, then try to record shift/left left left etc to select all the columns (as not all cells contain data). I print and naturally it prints perfectly, but when I run the macro it goes haywire, printing way up beyond the area selected. This is how that section of the recorded macro looks: Application.Goto Reference:="Total" (bottome edge of sheet, left hand side) Selection.End(xlUp).Select (goes to last entry) ActiveCell.Offset(0, 61).Range("A1").Select (goes to final column containing the 'rail') Selection.ClearContents (creates a break in the rail for next month) Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlUp)).Select (selects column up to break in rail at last month) ActiveCell.Offset(-120, -61).Range("A1:BJ121").Select (attempting to make the selection 3 dimensional to include all columns) ActiveCell.Activate Selection.PrintOut Copies:=1, Collate:=True (Bombs out, printing to about a month an a half further back than my topmost selection!) Can anyone tell me where I'm goimg wrong, or offer a solution? Thanks, Keith |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to end/up end/across to select print area?
Sorry, I should have mentioned I'm using Excel 2000 v.9
"Kevryl" wrote: This should be easy, but it won't work. I have a cash analysis spreadsheet that expands downwards as it goes, and I simply want to print the latest month off in a macro that closes the month and does all sorts of other complex stuff perfectly. I have created a "rail" on the right hand edge with a "|" character to end/up to a break in that column. With macro set to "relative" I record shift/end/up from an named anchor point to select the vertical area, then try to record shift/left left left etc to select all the columns (as not all cells contain data). I print and naturally it prints perfectly, but when I run the macro it goes haywire, printing way up beyond the area selected. This is how that section of the recorded macro looks: Application.Goto Reference:="Total" (bottome edge of sheet, left hand side) Selection.End(xlUp).Select (goes to last entry) ActiveCell.Offset(0, 61).Range("A1").Select (goes to final column containing the 'rail') Selection.ClearContents (creates a break in the rail for next month) Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlUp)).Select (selects column up to break in rail at last month) ActiveCell.Offset(-120, -61).Range("A1:BJ121").Select (attempting to make the selection 3 dimensional to include all columns) ActiveCell.Activate Selection.PrintOut Copies:=1, Collate:=True (Bombs out, printing to about a month an a half further back than my topmost selection!) Can anyone tell me where I'm goimg wrong, or offer a solution? Thanks, Keith |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to end/up end/across to select print area?
It looks to me as if near the end where you are saying "attempting to make
the selection 3-dimensional...) you are wiping out all the selections you've already made and made and are selecting A1:BJ121 If you are at BJ121 at that point, a -120 row and -61 column offset takes you back to cell A1. One way to see what is going on is to step through that code and watch what is happening on your spreadsheet. Right above the first line in that section insert a blank line and type the word Stop Then run your macro - it will go into debug mode with the word Stop highlighted. You can then use [F8] to single-step line by line through the code, keeping an eye on what is actually happening on the sheet. You may have to resize the VB Editor window to see your worksheet. Don't click on the worksheet while you're stepping through it - that may change where the current active cell/reference is at - You can save some paper by commenting out the Selection.PrintOut statement during the test (do that at the same time you insert the Stop command) "Kevryl" wrote: This should be easy, but it won't work. I have a cash analysis spreadsheet that expands downwards as it goes, and I simply want to print the latest month off in a macro that closes the month and does all sorts of other complex stuff perfectly. I have created a "rail" on the right hand edge with a "|" character to end/up to a break in that column. With macro set to "relative" I record shift/end/up from an named anchor point to select the vertical area, then try to record shift/left left left etc to select all the columns (as not all cells contain data). I print and naturally it prints perfectly, but when I run the macro it goes haywire, printing way up beyond the area selected. This is how that section of the recorded macro looks: Application.Goto Reference:="Total" (bottome edge of sheet, left hand side) Selection.End(xlUp).Select (goes to last entry) ActiveCell.Offset(0, 61).Range("A1").Select (goes to final column containing the 'rail') Selection.ClearContents (creates a break in the rail for next month) Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlUp)).Select (selects column up to break in rail at last month) ActiveCell.Offset(-120, -61).Range("A1:BJ121").Select (attempting to make the selection 3 dimensional to include all columns) ActiveCell.Activate Selection.PrintOut Copies:=1, Collate:=True (Bombs out, printing to about a month an a half further back than my topmost selection!) Can anyone tell me where I'm goimg wrong, or offer a solution? Thanks, Keith |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to end/up end/across to select print area?
Thanks, JL. I actually did step through and did watch the print area
selection line go awry, but I couldn't tell why it did. And yes, it appeared to me that it was turning absolute, but being largely unfamiliar with VB I wasn't sure, as VB does use apparently absolute range refernces to a relatively selected range (if that makes sense). At the point where the A1:BJ121 range appears, it was selected durin g the recording process by shift/left arrow movement, and the "relative reference" button was definitely depressed. But I have found previously that this button doesn't always work (for example you can't name a relatively selected range in a recorded macro without it turning absolute, a very powerful capability I used to use often in "Enable" and a source of great frustration that its not available in Excel. I wish you could tell me I'm wrong!). I can see I'm going to have to do a course in VB. I wonder if there's a good course online somewhere? Back to my problem: I cant end/left to make the selection 2 dimensional, because not all cells contain datya, and being a cash analysis it isnt possible to predict which ones will. I can't create a horizontal "rail" by filling in a row of cells because formulae above and below depend on continuity. I could copy in a triple row that would facilitate that, but it woukd be more complicated than it sounds due to a complex and quite sophisticated bank reconciliation system that uses a lot of conditional formatting, and the end of year reset macro would have to recopy in an entire worksheet of rows and formulae. Not that difficult but it does seem an awful long way around just to cope with an ornery printing issue! Thanks, and regards, Keith "JLatham" wrote: It looks to me as if near the end where you are saying "attempting to make the selection 3-dimensional...) you are wiping out all the selections you've already made and made and are selecting A1:BJ121 If you are at BJ121 at that point, a -120 row and -61 column offset takes you back to cell A1. One way to see what is going on is to step through that code and watch what is happening on your spreadsheet. Right above the first line in that section insert a blank line and type the word Stop Then run your macro - it will go into debug mode with the word Stop highlighted. You can then use [F8] to single-step line by line through the code, keeping an eye on what is actually happening on the sheet. You may have to resize the VB Editor window to see your worksheet. Don't click on the worksheet while you're stepping through it - that may change where the current active cell/reference is at - You can save some paper by commenting out the Selection.PrintOut statement during the test (do that at the same time you insert the Stop command) "Kevryl" wrote: This should be easy, but it won't work. I have a cash analysis spreadsheet that expands downwards as it goes, and I simply want to print the latest month off in a macro that closes the month and does all sorts of other complex stuff perfectly. I have created a "rail" on the right hand edge with a "|" character to end/up to a break in that column. With macro set to "relative" I record shift/end/up from an named anchor point to select the vertical area, then try to record shift/left left left etc to select all the columns (as not all cells contain data). I print and naturally it prints perfectly, but when I run the macro it goes haywire, printing way up beyond the area selected. This is how that section of the recorded macro looks: Application.Goto Reference:="Total" (bottome edge of sheet, left hand side) Selection.End(xlUp).Select (goes to last entry) ActiveCell.Offset(0, 61).Range("A1").Select (goes to final column containing the 'rail') Selection.ClearContents (creates a break in the rail for next month) Range(Selection, Selection.End(xlUp)).Select Range(Selection, Selection.End(xlUp)).Select (selects column up to break in rail at last month) ActiveCell.Offset(-120, -61).Range("A1:BJ121").Select (attempting to make the selection 3 dimensional to include all columns) ActiveCell.Activate Selection.PrintOut Copies:=1, Collate:=True (Bombs out, printing to about a month an a half further back than my topmost selection!) Can anyone tell me where I'm goimg wrong, or offer a solution? Thanks, Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to set print area for the entire excel workbook | Excel Discussion (Misc queries) | |||
Formula to govern Print Area | Excel Discussion (Misc queries) | |||
Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
Extra Row in Defined Print Area | Excel Discussion (Misc queries) | |||
Changing print area | Excel Discussion (Misc queries) |