How to end/up end/across to select print area?

[Guest]

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

 

[Guest]

Sorry... should have mentioned I'm working in Excel 2000 v.9.

 

[Guest]

Sorry, I should have mentioned I'm using Excel 2000 v.9

 

[Guest]

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)

 

[Guest]

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

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)

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