Excel VBA— Error '424' object required issue

Question

Hi I am completely to new VBA. I was trying create a button, which can automatically copy data from columns A-E on sheet1("Overview") to Sheet2 ("Example").

Below is the code I have copied from a tutorial video, but it ends up with an error 424 message--Object Required.

And the problem seems is with the lastrow: lastrow = Sheetoverview.Cells(Rows.Count, 1).End(xlUp).Row

Can anyone please help me with this problem? Thank you!

Sub copyconlumns()

Dim lastrow As Long, erow As Long

lastrow = Sheetoverview.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Sheetoverview.Cells(i, 1).copy
erow = sheetexample.Cells(Tows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheetoverview.Paste Destination = Worksheets("Sheetexample").Cells(erow, 1)

Sheetoverview.Cells(i, 2).copy
Sheetoverview.Paste Destination = Worksheets("Sheetexample").Cells(erow, 2)

Sheetoverview.Cells(i, 3).copy
Sheetoverview.Paste Destination = Worksheets("Sheetexample").Cells(erow, 3)

Sheetoverview.Cells(i, 4).copy
Sheetoverview.Paste Destination = Worksheets("Sheetexample").Cells(erow, 4)

Sheetoverview.Cells(i, 5).copy
Sheetoverview.Paste Destination = Worksheets("Sheetexample").Cells(erow, 5)

Next i

Application.CutCopyMode = False
sheetexample.Columns.AutoFit
Range("A1").Select

End Sub

John Coleman John Coleman · Accepted Answer · 2015-12-06T12:51:17

The dot operator in VBA accesses an object's property or method. Thus -- when you get the object required error in

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

there is a problem with what comes before a dot. In this case it is almost certainly Sheetoverview. Unless that is a global variable which has been given a value outside of the sub (which would be a problematic design), this is an undeclared variable which has never been set equal to a sheet. Your code is more likely to work if you replace Sheetoverview by Sheets("Overview") and sheetexample by Sheets("Example").

It is hard to over-emphasize the importance of placing the line Option Explicit at the top of each module in VBA. That forces you to declare your variables and allows the compiler to catch many errors like that one you have here. It is possible to configure your VBA editor to automatically insert that line in all new modules (Tools/Options/Require Variable Declatation).

Excel VBA— Error '424' object required issue

1 Answers