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

You need to qualify Cells and Rows with the worksheet they are referring to otherwise you're implicitly using the ActiveSheet. — Comintern
– Comintern, Commented Aug 8, 2018 at 3:45

Mathieu Guindon · Accepted Answer · 2018-08-08 03:45:52Z

3

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.

answered Aug 8, 2018 at 3:45

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

3 Comments

Nian Over a year ago

Thank you so much man, it worked. Now i learnt something, i will make that changes to my code, thanks for the advice once again.

GMalc Over a year ago

Mathieu, can you clarify a little more? Is it bad because using Application.Range bypasses the Workbook Object and Worksheet Object, thus the Range will be selected on which ever worksheet is currently active?

Mathieu Guindon Over a year ago

@GMalc Application.Range is implicitly Application.ActiveSheet.Range. Range as a member call, is always a member of a Worksheet object. Good code makes that Worksheet explicit ;-)

user9617878 · Accepted Answer · 2018-08-08 03:29:32Z

0

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

answered Aug 8, 2018 at 3:29

user9617878

2 Comments

Nian Over a year ago

"Set copyrange = ws2.Application.Range(Cells(i, 2), Cells(i, 6)).Copy" Set has been used in this line

Nian Over a year ago

May i know what brackets are you talking about? Can you show me?

Collectives™ on Stack Overflow

Excel VBA run-time Error '424' Object Required

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related