Finding insertion point in excel [Closed]

Report
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
Hello

here is a challenging one....

I am trying to automate a spreadsheet used for processing and compiling large amounts of data, with the outputs are always a range of data 5 columns wide, but of variable length. The range is selected and copied from a "processing" worksheet and pasted into a subsequent "outputs" worksheet. The outputs works sheet has 10 rows at the top occupied by data file information and formulas to act on the data being pasted.
Once a range or "block" of data has been inserted, the next selected block needs to be pasted adjacent to it (but separated by an empty column) ... an exact replication except that the input data is different.
This goes on until the the whole excel becomes filled with unique but identically formatted chunks of data.

What I really require is a macro that allows me to select a range from the first "processing worksheet" (I can do this bit) and then to recognise the first appropriate empty spot in the second "outputs" worksheet and paste it there (this has me stumped). These insertion points have fixed locations always separated by 7 cells/columns (ie: A:12, G:12, N:12, U:12 .... etc,etc), so I guess it is really a case of determining if the designated input cell is occupied by preexisting data ... with the option of either pasting (if it is empty) ... or moving across 7 cells (if it is full)

Many thanks for any help

1 reply

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Hello challenged,

to recognise the first appropriate empty spot in the second "outputs" worksheet, use the end property of VBA as in
Range("B4").End(xlDown).Select where column B is contiguous

to find insertion points have fixed locations always separated by 7 cells/columns (ie: A:12, G:12, N:12, U:12 .... etc,etc) use offset property
Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).value=Yourvalue

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!