CODEWITHSUNDEEP
vbaexcel
Srpic
Srpic

Reputation: 450

VBA - Copy data from one sheet to another

I'm trying to copy data from sheet "DATEV_RAB_UNVERBINDLICH", Range D2:D to sheet "Ready to upload", Range B2:B.

I would like find data to the last row and then copy them into the other sheet. I have this code:

Sub CopyInvoiceNo()

Dim ws, ws1 As Worksheet
Dim lastrow As Long

lastrow = .Cells(.Rows.Count, "D").End(xlUp).Row

    Set ws = Sheets("DATEV_RAB_UNVERBINDLICH")
    Set ws1 = Sheets("Ready to upload")
    ws.Range("D2" & lastrow).Copy
    ws1.Range("B4").PasteSpecial xlPasteValues
    ws1.Activate

End Sub

But I receive error msg Invalid or unqualified reference. Could you advise me, what do I do wrong, please?

Upvotes: 0

Views: 3305

Answers (1)

Moacir
Moacir

Reputation: 627

Two errors in your code

At line 3, this is not the correct way to define multiple variable types in the same line. You have to define each of them. At line 10, lets say your lastrow is "20". It would set the range to "D220", which is also not what you want.

Sub CopyInvoiceNo()

Dim ws As Worksheet, ws1 As Worksheet
Dim lastrow As Long
Set ws = Sheets("DATEV_RAB_UNVERBINDLICH")
Set ws1 = Sheets("Ready to upload")

    lastrow = ws.Cells(Rows.count, 4).End(xlUp).Row
    ws.Range("D2:D" & lastrow).Copy
    ws1.Range("B4").PasteSpecial xlPasteValues
    ws1.Activate

End Sub

I also changed to define the variables and its values at the start of the code. The lastrow bit didn't code here too after a bit of testing. Now it works for me at least.

Upvotes: 1

Related Questions