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

Question

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).

Please add to your code example or tidy it up. You've got End With and End If with no starting condition. Which line is it failing on? What is wrong? — Tom
– Tom, Commented Dec 2, 2015 at 13:13
Excuse me Tom. Total code was too long, giving an error. I approved your edit. Line that it is failing on is; Worksheets("ScrapLogboek").Cells(Rows.Count, "A").End(xlUp).Offset(1, -1). It works perfectly without this, but keeps overwriting the data found, and copied to another worksheet. To prevent this from happening i wanted to try it with the Offset function. — RobExcel
– RobExcel, Commented Dec 2, 2015 at 13:15
You aren't doing anything with the offset. Don't you want to select it or assign it a value? — Vulthil
– Vulthil, Commented Dec 2, 2015 at 13:17
You're telling the offset to go 1 down on the Row and 1 to the left on the Column. As you're doing this in Column A there is no where for it to go giving you the error. — Tom
– Tom, Commented Dec 2, 2015 at 13:17
Tom, that was pretty stupid. Thank you for that correction. @Alex4336, i want the Pasting function below it to look for the first empty row and paste there, instead of pasting in A2 everytime, overwriting everything. — RobExcel
– RobExcel, Commented Dec 2, 2015 at 13:20

R3uK · Accepted Answer · 2015-12-02 13:18:50Z

2

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

answered Dec 2, 2015 at 13:18

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RobExcel Over a year ago

You're my hero of the day! Thank you for teaching me how to handle the Offset, and even correcting my code. Bon Merci!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related