Find fiirst empty Cell in a Column and insert a new row

[LGMan]

Hi, I have a routine that copies all data from a large data sheet that contains repeated header rows and pastes into a Tem Data sheet, I then sort on a column to get the data that I'm interested in to appear in the top 1 to 118 rows, but the actual number of rows will be variable as the next time I run the code, there may only be 25 rows of 'valid' data.
The next cell in Column C below my 'valid' data will be empty.
My next step is to copy the 'valid' data to a new sheet, so my plan is to insert a new row where the first empty cell occurs, then I can use Range("A1").CurrentRegion.copy to get the data into the new sheet.

So although I can find the first empty cell in Col C, I haven't been able to insert a new row.

any help greatly appreciated, or perhaps there is a better way that I hasn't occured to me?

 

[SkipVought]

Hi,

On the sheet that you paste into, (I'm calling it Sheet2)...

'Copy from your target sheet, then...
   Sheet2.Cells(1,1).End(xlDown).Offset(1).PasteSpecial xlPasteAll

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Hi Skip, I get a runtime error 1004, 'to paste all cells from an Excel Worksheet into the current worksheet, you must paste into the first cell (A1 or R1C1).

I've copied the data from Sheet1 into Sheet2, and tried your code

Thanks

 

[SkipVought]

Please post your code

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Hi Skip, I found that I had a problem with Sheet2, which meant all my attempts to insert a row threw an error which I thought was my code. For the time being, I've gone for a simple approach such as using find "" within Col C and then insert the row. As this has got my process working. I can now look to improve and rationalise the code and once I've done so I can post here. Thanks for the offer to help

 

[SkipVought]

There is an assumption in my solution that I suppose I should have stated. I assumed that column A on Sheet2 has a value in every row.

A better approach might be...

'Copy from your target sheet, then...
   With Sheet2.Cells(1,1).CurrentRegion
      .Cells(.Rows.Count + 1, 1).PasteSpecial xlPasteAll 
   End With

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}