Paste Links

[parry]

Hi, Im wanting to copy a range and paste as a link.

The Excel Macro recorder would create something like this...

Range("A1:A3").Select
    Selection.Copy
    Range("D1").Select
    ActiveSheet.Paste Link:=True

How can you paste a link without first selecting the destination cells? Pasting a link is only available with a sheet object so how do I include the destination range? There is no link argument for the copy method.

 

[Legacy 21301]

According to the VBE Help file it cannot be done.

Have a look at Paste Method (Worksheet Object).

 

[parry]

Thanks Ponsy, just as I suspected then. Although correct in most instances its a bit of a fallacy that you dont have to select objects to work with them as in some cases thats not correct. Unless you know how to use an expression which includes a range and that Excel will accept?

 

[Chitosunday]

But isn't it the same as our typical formula :Sub test()
Range("d1:d3").Formula = "=a1"

 

[parry]

But isn't it the same as our typical formula :Sub test()
Range("d1:d3").Formula = "=a1"

Yes, thank you as thats the method I should use. It just seems strange that I have to use an alternative way to achieve the result where theres a specific method of doing a paste as a link but it appears it only can be used by selecting a cell first. I just wanted to confirm that.

Im just trying to understand what code you should use when copying and pasting as I prefer to use those which dont require me to select something first.

So if Im wanting to do a straight copy to another range I use...
Range("A1:A10").Copy Range("C1:C10")

But if Im wanting to copy as a link I need to use...
Range("A1:A10").Copy
Range("C1").Select 'or Range("C1:C10").Select
ActiveSheet.Paste Link:=True

Or to link without selecting...
Range("C1:C10").FormulaR1C1 = "=RC[-2]"

EDIT:
I tried to use the destination argument with ActiveSheet.Paste but this fails with an err 1004 Worksheet object class failed.

Sub test()
Range("A1:A10").Copy
ActiveSheet.Paste Destination:=Range("C1:C10"), Link:=True
End Sub

 

[NateO]

My two cents:

Avoid Excel's Paste Special functionality with respect to VBA code like it's the plague.

Link a cell and autofill, or copy normally. MS has bolloxed up the functionality of Paste Special in VBA in terms of what could have been nice native functionality. No need to figure it out, it's crazier than fiction, look elsewhere. I take my own medicine here and look for other ways to accomplish my goal sets.

 

[parry]

Thank you Nate. Im not sure if you saw my edit so do you know why this bombs or is this bolloxed as well?

Sub test() 
Range("A1:A10").Copy 
ActiveSheet.Paste Destination:=Range("C1:C10"), Link:=True 
End Sub

 

[NateO]

I don't know, and not my point. Here's my point:

The PasteSpecial Method or anything coming from the P.S. dialog is flawed with respect to Range Objects and Selecting. With respect to VBA, don't use it, I don't.

 

[parry]

Thanks Nate, I appreciate your comments. I'll just use FormulaR1C1 if I want to link a cell.

 

[Legacy 21301]

Thank you Nate. Im not sure if you saw my edit so do you know why this bombs or is this bolloxed as well?

Sub test() 
Range("A1:A10").Copy 
ActiveSheet.Paste Destination:=Range("C1:C10"), Link:=True 
End Sub

It doesn't work because MS didn't allow for such a method.

As previously posted, the Help file specifically states for the Paste Method that :
- if the Link argument is specified, the Destination argument cannot be used, and
- If the Destination argument is specified, the Link argument cannot be used, and
- If the Destination argument is not specified, the destination range must be selected before the Paste Method is used.

It does seem odd that it has to be done like this, but at least the Help file explains it.