Member Avatar for ggeoff

Hi

I thought this was easy. I want to copy a spreadsheet in a workbook to a new spreadsheet in the same workbook preserving links.
So as the source worksheet received new entries the copied spreadsheet would be updated.

My reason being that I would introduce some variation in the way that data was computed on a few lines.

  • 3 Contributors
  • 3 Replies
  • 293 Views
  • 8 Months Discussion Span
  • Latest Post Latest Post by bobbyraw

Dani AI

Generated

A few targeted options that will give you a live copy (updates when the source changes) while letting you change a few rows of calculation.

Copying a worksheet tab just duplicates its formulas and relative references — that usually results in the copy referencing itself, not the original sheet. To get a live mirror you need explicit links or a linked-sheet approach rather than a straight tab duplicate. (support.microsoft.com)

Simple (no code): select the source range, Copy, go to the new sheet and use Home > Paste > Paste Special > Paste Link. That produces formulas like =Sheet1!A1 that update automatically when the source changes. For very large ranges, switch Calculation to Manual before pasting (File > Options > Formulas or Formulas > Calculation Options) so Excel does not attempt to recalculate everything during the paste; paste in smaller blocks if necessary. (support.microsoft.com)

If Paste Link is impractical (resource/time errors) a small VBA routine can create a new sheet and populate every cell with a direct link to the source (this preserves formatting too and lets you then edit only the rows you want to compute differently). Put this in a standard module and run while the source sheet is active (backup first):

Sub CreateLinkedCopy()
    Dim wsSrc As Worksheet, wsDst As Worksheet, c As Range
    Dim srcName As String, dstName As String, i As Long, suffix As Long

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Set wsSrc = ActiveSheet
    srcName = wsSrc.Name
    dstName = srcName & "_Linked"
    suffix = 1
    Do While SheetExists(dstName)
        dstName = srcName & "_Linked" & suffix: suffix = suffix + 1
    Loop

    Set wsDst = ThisWorkbook.Worksheets.Add(After:=wsSrc)
    wsDst.Name = dstName

    ' copy formats & column widths
    wsSrc.Cells.Copy
    wsDst.Range("A1").PasteSpecial xlPasteFormats
    wsDst.Range("A1").PasteSpecial xlPasteColumnWidths
    Application.CutCopyMode = False

    ' make every cell a link back to source
    For Each c In wsSrc.UsedRange.Cells
        wsDst.Range(c.Address).Formula = "='" & srcName & "'!" & c.Address(False, False)
    Next c

CleanExit:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
End Sub

Function SheetExists(sName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sName)
    SheetExists = Not ws Is Nothing
    On Error GoTo 0
End Function

Notes, gotchas and troubleshooting: INDIREC T-style dynamic references are possible but are volatile and can slow large workbooks — prefer direct =Sheet1!A1 links when performance matters. Back up before running macros; array formulas, named ranges, charts, or merged cells may need extra handling. If you repeatedly hit memory/time errors on very large workbooks, consider using a 64‑bit build of Excel or reducing the size of the copied ranges (64‑bit can address much more memory). (exceljet.net)

Acknowledgement: ’s copy/paste idea is the simplest for small ranges; ’s formula-view trick helps when you want to copy raw formulas — for a true live mirror that you can selectively alter, use Paste Link or the VBA approach above.

Recommended Answers

All 3 Replies

Member Avatar for DaveAmour

Can you not just copy and paste?

Edited by DaveAmour
Member Avatar for ggeoff

Thanks DaveAmour. I have tried but Paste Special is by degault set to "All" but that does not include the links in each cell to to the copied worksheet. If I sealect "paste link". THe program cannot make the changes reports the time it would take and then fails due to lack of resources. I am only running Outlook and Excel.

Member Avatar for bobbyraw

i want to make sure i understnd the request. you want to copy all data in lets say sheet 1 to sheet 4 and maintain all your formulas. is that correct? and if you ricg click sheet 1 and choose copy or move and seleft sheet 4 it does not work. am i corerct here also.
the next thing you can do is press Ctrl+~ this will show all your formulas, then press Ctrl+A to select every thing. then go to sheet 4 and select cell A1 and then press Ctrl+V this past all your formulas in the new sheet 4, then you can press Ctrl+~ to hide the formulas again.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.