How is data in one sheet copied under the last data on another sheet with VBA?

[ashotinthedark]

Hi everyone !

I have some data in my "QSYS" sheet with 3 columns and I want to copy them to another "MasterListe" sheet under the last data. (Code should find first row that is empty and paste new data there) I need a VBA code for this purpose.
I wrote this but it does not work.

Dim lastrow As Long
lastrow = Sheets("MasterListe").Range("A65536").End(xlUp).Row - 1
Sheets("QSYS").Range("A:C" & Range("A" & Rows.Count).End(xlUp).Row).Copy Destination:=Sheets("MasterListe").Range("A:C" & lastrow)

Could you help me please ? I uploaded an example.
For instance, I have a situation like first photo and second photo and I want to merge them like third photo.

Thank you !

 

[Fluff]

Hi & welcome to MrExcel.
Change the -1 to +1, if that does not help, then please explain what is wrong with the code.

 

[jasonb75]

There are a few things wrong in your code that I've noticed, maybe some more that I've missed (without testing it).

Firstly, your definition of lastrow, I've used a different method that is compatible with newer versions of excel, the method you used was for excel 2003 or older. It will work with newer versions but not correctly if you have more than 65536 rows of data. I notice that you used similar in the copy section, when writing code it is always a good idea to be consistent with your methods so that you don't confuse yourself.

Also, you need to add 1 to the last row, not subtract 1.

I both the copy range you had only included the end row, not the start row. You had done similar with the paste range, although slightly less critical. While the syntax was incorrect, the paste range only needs to refer to the top left cell. It will automatically resize to match the range that was copied.

Dim lastrow As Long, lr As Long
lastrow = Sheets("MasterListe").Cells(Rows.Count, 1).End(xlUp).Row +1
With Sheets("QSYS")
    lr = .Cells(Rows.Count, 1).End(xlUp).Row)
    .Range("A1:C" & lr).Copy Destination:=Sheets("MasterListe").Range("A" & lastrow)
End With

 

[ashotinthedark]

There are a few things wrong in your code that I've noticed, maybe some more that I've missed (without testing it).

Firstly, your definition of lastrow, I've used a different method that is compatible with newer versions of excel, the method you used was for excel 2003 or older. It will work with newer versions but not correctly if you have more than 65536 rows of data. I notice that you used similar in the copy section, when writing code it is always a good idea to be consistent with your methods so that you don't confuse yourself.

Also, you need to add 1 to the last row, not subtract 1.

I both the copy range you had only included the end row, not the start row. You had done similar with the paste range, although slightly less critical. While the syntax was incorrect, the paste range only needs to refer to the top left cell. It will automatically resize to match the range that was copied.

Dim lastrow As Long, lr As Long
lastrow = Sheets("MasterListe").Cells(Rows.Count, 1).End(xlUp).Row +1
With Sheets("QSYS")
    lr = .Cells(Rows.Count, 1).End(xlUp).Row)
    .Range("A1:C" & lr).Copy Destination:=Sheets("MasterListe").Range("A" & lastrow)
End With

Yes! I got it perfectly. Thank you !