Copy range to other sheet with reference to the original range

Question

I have a "template"-sheet within my document. On this sheet there is a table that calculates values from all over the document. It looks like something: sheet2_A1 + sheet3_A3 + sheet5_B2 ,...

(Let's say) On sheet1 I want, in some cases, to copy this table from the template to my sheet1. When I do this code:

Sheets("template").Range("table").Copy Range("sheet1_paste_location")

Everything is copied, but also the formulas. What I would like is every value is a reference to the cell from where I have copied. So more something like in cell A1 on the sheet1 : "template_A1,..." instead of "sheet2_A1 + sheet3_A3 + sheet5_B2 ,..." I first did (after I've copied the table) with a for each in every cell, but the code is somewhat "lagging" a bit, is there not a way to copy with a reference with some kind of parameter ? I can't imagine there is not something like this in VBA.

Tim Williams · Answer 1 · 2021-09-29T15:40:43.920

1

You can use Link:=True when pasting

Sheets("template").Range("table").Copy 
with Sheets("Sheet1")
    .select
    .Range("sheet1_paste_location").select
    .Paste Link:=True
end with

https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.paste#:~:text=True%20to%20establish%20a%20link%20to%20the%20source%20of%20the%20pasted%20data.

EDIT: or you can do something like this to link the ranges

Sub Tester()

    LinkRanges Workbooks("Book2").Sheets("Sheet1").Range("A1:B9"), _
               Activesheet.Range("F10")

End Sub

'Link rngDest to rngSrc using an array formula
Sub LinkRanges(rngSrc As Range, rngDest As Range)
    Dim f
    
    If rngSrc.Parent.Parent.Name <> rngDest.Parent.Parent.Name Then
        'different workbooks
        f = "=" & rngSrc.Address(False, False, external:=True)
    Else
        'different worksheets?
        f = "=" & IIf(rngSrc.Parent.Name <> rngDest.Parent.Name, _
                "'" & rngSrc.Parent.Name & "'!", "") & _
            rngSrc.Address(False, False)
    End If
    With rngSrc
        'make sure destination and source ranges are matched in size
        rngDest.Cells(1).Resize(.Rows.Count, .Columns.Count).FormulaArray = f
    End With
End Sub

edited Sep 29 '21 at 15:40

answered Sep 27 '21 at 15:33

Tim Williams

154,628
8
97
125

Thanks but it's not working yet :( in your example you use the "with" and the "select" command. It is possible to avoid these ? The range "sheet1_paste_location" is only one cell, while the Range("table") is a larger 'zone', so when I run your code only the first cell is being overwritten, while the other fields stay unmodified. The "with" is not an issue, but the select is, because I would like to keep my focus where it was instead of moving it. I tried something: "Activesheet.paste Destination:=Range("sheet1_paste_location") link:=true" but I get an error, probably too much parameters. – smirnoff103 Sep 28 '21 at 06:57
@smirnoff103. I faced this issue before and solved by this excellent answer to avoid select while coping range as link https://stackoverflow.com/questions/69250706/how-to-avoid-select-with-copy-named-range/69251393?noredirect=1#comment122400508_69251393 – Leedo Sep 28 '21 at 08:57
In the link I posted it states about the `Link` parameter: "True to establish a link to the source of the pasted data. **If this argument is specified, the Destination argument cannot be used.** " – Tim Williams Sep 28 '21 at 15:17
@TimWilliams Ok, but how can you give a "start range" (with this I mean the cell from where you will start pasting), if I leave the destination argument it just paste the content of the clipboard where my focus is. – smirnoff103 Sep 29 '21 at 09:20
It starts pasting at the selected cell. If you don't want to see the switch and you want to maintain your current selection then store that in a variable while you perform the paste, then switch back when done. – Tim Williams Sep 29 '21 at 15:22

Copy range to other sheet with reference to the original range

1 Answers1