Find first empty row and copy/paste data

[Faygin]

Looked around and tried to modify existing code but without success.

I have a workbook with a command button.
When i press the button i want to copy the data and paste it in another workbook.

But it has to be on the first available empty row, so that i dont destroy the old data.

So far the code looks like this:

Private Sub CommandButton1_Click()

'This is the data i want to copy
Range("E3:M8").Select
Selection.Copy

'Open the workbook which i want to paste into
Workbooks.Open "O:\abc\def\ghi.xlsx"

' Select the sheet and range A1
Application.Goto Workbooks("NPD Beläggningsprofil.xlsx").Sheets("DATA").Range("A1")

--
Now i want to find the first empty row on the sheet "DATA" and paste or copy the code from the other worksheet.
If the first cell in the row is empty, then i consider the whole row as empty and can be written data to.

Any suggestions and pointers are much appreciated!

 

[MARK858]

Do you mean the first empty row or the first empty row after the data?

 

[Faygin]

The sheet "DATA" contains a lot of data.
I want to find the first empty row and paste additional data into the sheet.

 

[MARK858]

That didn't answer my question. With the below would want row 3 or row 6?

 

[Faygin]

OK, now i understand, sorry about that.
In the sheet there are never any "gaps".
All data are from top to bottom.

So in my case (referring to your example) row three would never exist.
So row three would contain data and i would like new data to appear in row 6.

 

[MARK858]

A bit confused why you are opening one workbook then going to another workbook to paste the data but anyway try the below (then if you want the other workbook open do it afterwards).
There is no need to go to the sheet or activate it.

Range("E3:M8").Copy Workbooks("NPD Beläggningsprofil.xlsx").Sheets("DATA").Cells(Rows.Count, "A").End(xlUp)(2)

 

[Faygin]

Thank you, think this is very close to a solution.

When i first tried i got no error messages and maybe its because i had the two workbooks opened?
If i have the target workbook closed i get a error message:

Subscript out of range (Error 9)

Private Sub CommandButton1_Click()

 
 Range("E3:M8").Copy Workbooks("NPD Beläggningsprofil.xlsx").Sheets("DATA").Cells(Rows.Count, "A").End(xlUp)(2)
    
    
End Sub

 

[MARK858]

You would get an error as to refer to a closed workbook you would have to use its full path.
Personally I would open and close it in the code as there are lots of things that you can't do with closed workbooks.

 

[Faygin]

Cant thank you enough, problem is solved and you made my day!

 

[MARK858]

You're welcome