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

	A:I	J:AG
1	unneeded information	needed data set1
2	unneeded information
3	unneeded information
4	unneeded information	needed data set 2

<tbody>
</tbody>

Results worksheet

	A:X
1	needed data set 1
2	needed data set 2

<tbody>
</tbody>

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.