Beginner in VBA and I am having a compile error object not required for the following code

[ambepat]

I am getting a "compile error object required" at set lastrow. Not sure how to fix it so any help will be really appreciated. Thanks.

Sub copydata()

Dim lastrow As Long
Dim CrossTBrow
Dim i As Long




Set lastrow = Worksheets("Trial Balance").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To lastrow


Worksheets("Trial Balance").Cells(i, 1).Copy
CrossTBrow = Worksheets("Cross Tabular Raw Data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Worksheets("Trial Balance").Paste Destination:=Worksheets("Cross Tabular Raw Data").Cells(CrossTBrow, 1)



Next i

 

[Michael M]

Remove the word "Set"

lastrow = Worksheets("Trial Balance").Cells(Rows.Count, 1).End(xlUp).Row

 

[ambepat]

Remove the word "Set"

lastrow = Worksheets("Trial Balance").Cells(Rows.Count, 1).End(xlUp).Row

Hi Michael,

Thanks for your reply.

When I remove the set I have a Run-time error 9 - Subscript out of range. Also please explain why you will remove the set?

Thanks for your help. Really appreciate it.

 

[Michael M]

"Subscript out of range"

Usually means that one of your sheets names in the code doesn't match the actual Sheet name...check for typos, spelling, and excess spaces...leading or trailling.

Have a look at this link to get a better understanding of the "Set" keyword.
It is used to create a new object, but in your case you only need to return an Integer value



https://stackoverflow.com/questions/349613/what-does-the-keyword-set-actually-do-in-vba

 

[ambepat]

"Subscript out of range"

Usually means that one of your sheets names in the code doesn't match the actual Sheet name...check for typos, spelling, and excess spaces...leading or trailling.

Have a look at this link to get a better understanding of the "Set" keyword.
It is used to create a new object, but in your case you only need to return an Integer value



https://stackoverflow.com/questions/349613/what-does-the-keyword-set-actually-do-in-vba

Thanks for you help. Appreciate it.