VBA Copy Method Error

Question

I am running a VBA macro that involves multiple copy-paste processes. However, using .copy (destination) give a "Copy method of Range class failed." Here's that code.

Dim Prop As Range
Dim propo As Range
Set Prop = sha.Range("B6")
Set propo = Sheets("Summary").Range("B1")
Prop.Copy (propo)

However, I have found an odd work-around to this issue. Instead of .copy (destination) I use .copy and then .PasteSpecial:

Dim Prop As Range
Dim propo As Range
Set Prop = sha.Range("B6")
Set propo = Sheets("Summary").Range("B1")
Prop.Copy
propo.PasteSpecial (xlPasteValues)

Any idea why this was necessary? I would prefer to keep it simple going forward.

remove the () around propo

Scott Craner
– Scott Craner

2015-12-17 18:52:34 +00:00
Commented Dec 17, 2015 at 18:52 — Scott Craner
– Scott Craner, Commented Dec 17, 2015 at 18:52
Thanks Scott! I'm surprised it was that simple

Tollbooth
– Tollbooth

2015-12-18 15:12:34 +00:00
Commented Dec 18, 2015 at 15:12 — Tollbooth
– Tollbooth, Commented Dec 18, 2015 at 15:12

Branden Keck · Accepted Answer · 2015-12-17 18:54:04Z

3

It appears that the reason this was necessary was that your syntax was incorrect. In the Microsoft doc for Range.Copy it lists that to add a destination, your code should be:

Worksheets("Sheet1").Range("A1:D4").Copy _ 
    destination:=Worksheets("Sheet2").Range("E5")

As shown here: https://msdn.microsoft.com/en-us/library/office/ff837760.aspx

answered Dec 17, 2015 at 18:54

Branden Keck

7125 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

VBA Copy Method Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related