Find the next non empty cell in a column and copy specific cells across worksheets

[spill-the-beans]

Hi

My workbook has 2 sheets - one named Data, one named Results.

What I need to happen is, excel should look in column J in the Data worksheet. When it finds the next non empty cell, it should copy across the row values for columns J to AG of the Data worksheet into columns A to X of the Results worksheet.

The Results copied rows should be one below the other, but in the Data, the rows to copy are spaced out. The information in A:I of Data should not be copied across to Results.

So..

Data worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A:I[/TD]
[TD]J:AG[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]unneeded information[/TD]
[TD]needed data set1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]unneeded information[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]unneeded information[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]unneeded information[/TD]
[TD]needed data set 2[/TD]
[/TR]
</tbody>[/TABLE]

Results worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A:X[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]needed data set 1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]needed data set 2[/TD]
[/TR]
</tbody>[/TABLE]

Help would be very much appreciated!

 

[Andrew Poulsom]

Do you have headings in row 1 of the Data worksheet? If so you can AutoFilter column J for NonBlanks and copy the visible rows to the Results worksheet.

 

[spill-the-beans]

Yes, that works, but I only need the cells in the row that have information in, rather than copying across blank cells. For example, the cell in Data column Q is deliberately left empty, because the cell in Results column Q has a formula in it.

If I manually copy, I copy across the Data blank cell over the Results formula. This is why I think I need a formula or script to only copy across cells that have values in.

 

[Andrew Poulsom]

You said:

What I need to happen is, excel should look in column J in the Data worksheet. When it finds the next non empty cell, it should copy across the row values for columns J to AG of the Data worksheet into columns A to X of the Results worksheet.

There's no mention of column Q there.

 

[spill-the-beans]

You're right, I didn't mention it. I was trying not to complicate the question too much. By "row values" I meant that on that row, the cells with values in them should be copied over.