Finding insertion point in excel

Closed
challenged - Jul 16, 2009 at 02:16 AM
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
- Jul 17, 2009 at 05:14 AM
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

Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Jul 17, 2009 at 05:14 AM
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
0