Excel VBA run-time Error '424' Object Required

Question

I've encountered a problem run-time Error '424' object required. This is the solution i've acquired from my previous post, after spending some time to troubleshoot, i still can't solve it. Thanks to @rawrplus for the solution and I am still learning Excel vba.

Link: Excel VBA Cutting rows from workbook1 and paste it in workbook2

Option Explicit
Private Sub table_to_table()
'Declaration
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set wb1 = Workbooks.Open("C:\Documents and Settings\lye.yan.nian\My  Documents\testingmacro2.xlsx")
Set wb2 = Workbooks.Open("C:\Documents and Settings\lye.yan.nian\My Documents\testingmacro3.xlsx")
Set ws1 = wb1.Sheets("Test2")
Set ws2 = wb2.Sheets("Test1")

Dim res_lr As Long
Dim lr2 As Long
lr2 = ws2.Cells(Rows.Count, 2).End(xlUp).Row


MsgBox lr2   /* Testing is done as i can get this value */

Dim r As Range, ary
Set r = ws1.Application.Range(Cells(1, 2), Cells(1, 6)) /* Tested working as i can get this value too*/
ary = Application.Transpose(Application.Transpose(r.Value))
MsgBox Join(ary, " ")


Dim copyrange As Range
Dim i As Long /* This is declared */
For i = 2 To lr2

MsgBox i /* i did some testing on this and removed the below code */
Set copyrange = ws2.Application.Range(Cells(i, 2), Cells(i, 6)).Copy /* This is the part where i got the error i can't figure out why*/ 

res_lr = ws2.Cells(Rows.Count, 8).End(xlUp).Row
ws2.Range(Cells(res_lr, 8), Cells(res_lr, 12)).PasteSpecial xlPasteValues

Set copyrange = ws1.Application.Range(Cells(i, 2), Cells(i, 6)).Copy

ws2.Range(Cells(res_lr + 1, 8), Cells(res_lr + 1, 12)).PasteSpecial xlPasteValues

Next i

wb1.Close

End Sub

Answer 1

Set copyrange = ws2.Application.Range(Cells(i, 2), Cells(i, 6)).Copy

Range.Copy does not return a Range object reference that you can assign to a Range object variable. It's a member method that takes the contents of a range, and copies it into the clipboard.

Your copyrange isn't the result of the .Copy operation, it's the result of a ws2.Application.Range(...) call... which is malformed.

This might fix it¹:

Set copyrange = ws2.Application.Range(Cells(i, 2), Cells(i, 6))
copyrange.Copy

....and this is what you probably meant to be doing:

Set copyrange = ws2.Range(ws2.Cells(i, 2), ws2.Cells(i, 6))
copyrange.Copy

---

¹ it's bad code, because you have an explicit Worksheet reference (ws2), and from there you go to Application to grab whatever worksheet is currently active, and work off that - so you think you're working off ws2 when in reality you're working off whatever ActiveSheet currently happens to be. Same with the unqualified Cells calls, which implicitly refer to Application.ActiveSheet.Cells.

Answer 2

Try adding the word set on the line where it throws error. Example: set x = 'something