CODEWITHSUNDEEP
vbaexcel
RobExcel
RobExcel

Reputation: 191

Copy to first empty row of a different worksheet with Offset function

This macro is intended to via an Inputbox using Findstring, find a row, copy it to the first empty row of a different worksheet, and delete the row on the original worksheet.

If Not Rng Is Nothing Then
    Rng.EntireRow.Copy
    Worksheets("ScrapLogboek").Cells(Rows.Count, "A").End(xlUp).Offset(1, -1)
    Worksheets("ScrapLogboek").Range("A2").PasteSpecial
    Rng.EntireRow.Delete
Else
    MsgBox ("Niks gescrapt. Check of het B-nummer correct is.")
End If

End With
    MsgBox ("Logboek gescrapt.")
End If
End Sub

I get a syntax error on the Offset function.

Worksheets("ScrapLogboek").Cells(Rows.Count, "A").End(xlUp).Offset(1, -1).

Upvotes: 2

Views: 426

Answers (1)

R3uK
R3uK

Reputation: 14537

You are in column A, so you can't Offset minus one column because there is none other on the left!

Try your code with Worksheets("ScrapLogboek").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) and it'll work! ;)

And your code would always paste on A2 which is probably not the purpose, so here is the corrected version :

If Not Rng Is Nothing Then
    Rng.EntireRow.Copy
    Worksheets("ScrapLogboek").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
    Rng.EntireRow.Delete
Else
    MsgBox ("Niks gescrapt. Check of het B-nummer correct is.")
End If

End With
    MsgBox ("Logboek gescrapt.")
End If
End Sub

Upvotes: 2

Related Questions