While debugging VBA code, i am getting an erorr saying "Object required for the 8th line

Question

Sub copyNonblankData()

Dim erow As Long, lastrow As Long, i As Long

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow

    If Sheet1.Cells(i, 1) <> "" Then
    ' i'm assuming the next line is the 8th line?
    Sheets("Sheet1").Range(Cells(i, 1), Cells(i, 2)).Copy
    Sheets("Sheet2").Activate
    ' error occurs here
    erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row.Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Sheets("Sheet2").Range(Cells(erow, 1), Cells(erow, 2))
    Sheets("Sheet1").Activate
    End If

Next i

Application.CutCopyMode = False

End Sub

Answer 1

The Offset property in Excel VBA takes the range which is a particular number of rows and columns away from a certain range.

Sheet2.Cells(Rows.Count, 1).End(xlUp).Row will give you Long and not a Range Object.

Your code can be written as

Dim rng As Range
Set rng = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

erow = rng.Row

Or simply as

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1

Also your code will work if the relevant sheet is active. You need to fully qualify your cells as shown in Why does Range work, but not Cells?

TIP

If you are using CodeName then use CodeName and avoid using the Name of the sheet. And if you are using Name then avoid using Codename. You will end up getting confused. If you do not know the difference between them, then you may want to see Refer to sheet using codename

Remember you will get the same error (Object required) again if the Codename doesn't exist. BTW if the Name of the sheet doesn't exist then you will get a different error (Subscript Out Of Range).

so if erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1 gives the same error then that means Sheet2 doesn't exist. Try with

erow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1