vba code to paste link problem

Question

I have the following code to copy a range and paste it as a link and it works as it should and pastes the links to the new worksheet.

    .Range(.Cells(2, "A"), .Cells(lngLastRow, lngBldDateCol)).Copy

    wsCombData.Select

    Range("A2").Select

    ActiveSheet.Paste Link:=True

However, I don't like having to select the destination worksheet and cell but if I try to paste it the following way without selecting the destination worksheet and cell then on the Paste line I get "Runtime error 438: Object doesn't support this property or method."

 .Range(.Cells(2, "A"), .Cells(lngLastRow, lngBldDateCol)).Copy

 wsCombData.Range("A2").Paste Link:=True

Am I missing something here or can I only paste a link by first selecting the destination worksheet and cell.

Answer 1

To paste a link, you need to select the destination, I'm afraid.

Answer 2

I know this question has been asnwered but I have a solution to your problem that does not make you select the workbook where you are pasteing the link to.  You would however have to select the range first.  This may not work for you but when I came across your question then found my answer I thought I would share

Range(.Cells(2, "A"), .Cells(lngLastRow, lngBldDateCol)).Select    

 wsCombData.Range("A2").value = ("=" & ActiveSheet.Name & "!" & ActiveCell.Address)

 I don't know if this will help but just thought I would share.

Answer 3

Thank you Hans. You have confirmed my thoughts on this.

Answer 4

Hello Jonathan,

Thank you for the reply. I haven't tested but looking at your code I believe that it will only create a link for one cell; that is the ActiveCell. I wanted to paste a range as a link to the base data.

Don't confuse ActiveCell with Selected range. The ActiveCell of a Selected range is a single cell; that is the first cell selected.

My original question was to confirm my thoughts on the subject and after it was confirmed I created a work around for my problem. I have posted the code below. It actually uses a similar method to your post to provide a single cell link and then copies that linked cell and pastes it to the remainder of the linked range which achieves the desired outcome. However, it uses Address(0, 0) so that it does not place an absolute address with the $ signs in the first cell.

Note that neither the original base data nor the destination for the link needs to be selected.

Sub CopyPasteLink()

  Dim wsBaseData As Worksheet

  Dim wsLinkedData As Worksheet

  Dim rngFirstCell As Range

  Dim rngLinked As Range

  Dim lngRowOffset As Long

  Dim lngColOffset As Long

  Set wsBaseData = Sheets("Base Data")

  Set wsLinkedData = Sheets("Linked Data")

  With wsBaseData

    Set rngFirstCell = .Cells(3, "C")

    'Save difference between first row of range _

     and last row of range to use as Offset below.

    lngRowOffset = .Cells(.Rows.Count, rngFirstCell.Column).End(xlUp).Row _

                    - rngFirstCell.Row

    'Save difference between first column of range _

     and last column of range to use as Offset below.

    lngColOffset = .Cells(rngFirstCell.Row, .Columns.Count).End(xlToLeft).Column _

                    - rngFirstCell.Column

  End With

  With wsLinkedData

    'Assign first cell of destination (linked) range to a range variable

    Set rngLinked = .Range("F8")  'First cell of linked data

  End With

  'Link the first cell of linked range back to first cell of base data.

  'Single Quotes surrounding sheet name in case sheet name has spaces.

  'Must use A1 style addressing hense Address(0, 0)

  rngLinked.Formula = ("='" & rngFirstCell.Parent.Name & "'!" _

                      & rngFirstCell.Address(0, 0))

  'Copy the first linked cell formula to the remaining linked range _

   using the offset row and column values saved above.

  rngLinked.Copy Destination:=Range(rngLinked, _

                  rngLinked.Offset(lngRowOffset, lngColOffset))

End Sub