Macro in Excel to Copy a Worksheet (by referencing every cell)

Question

I've found lots of examples for Copying Worksheets in VBA, or replacing formulas with values etc. What I want to do is copy an entire worksheet, but on the new worksheet, every cell refers back to its original. So in cell A1 of the new worksheet, it would simply have the formula "='Sheet1'!A1"

Is there an easy way to do this? Thanks

P.S. I need it to be a Macro, as I need to be able to run it on specific sheets, to copy all the cells from that sheet into a new one, not always from "Sheet1"

Yes, copy the desired cells then paste special. there is an option to paste links. — Scott Craner, Sep 19 '16 at 13:32
see here: https://msdn.microsoft.com/en-us/library/office/ff835858.aspx?f=255&MSPPError=-2147217396 — Scott Craner, Sep 19 '16 at 13:33
Hi Scott, thanks, this could be what I need. Presumably there is a way I can do this via VBA? — Dave, Sep 19 '16 at 13:34
Another thing to try is the macro recorder. turn it on then manually copy and paste just the links to another sheet and see how the recorder uses the terminology then mimic that with your specific needs. — Scott Craner, Sep 19 '16 at 13:39

score 1 · Answer 1 · answered Sep 19 '16 at 13:40

1

You nearly gave the answer in your question:

sheet2.range("A1:F50").formula = ='Sheet1'!A1"

answered Sep 19 '16 at 13:40

iDevlop

24,841
11
90
149

wow, very simple! Is there a way to stop it putting "0" in all the previously blank cells? – Dave Sep 19 '16 at 13:45
@ScottCraner formula **will** change since "A1" is a relative address. – iDevlop Sep 19 '16 at 14:01
so it does. My bad – Scott Craner Sep 19 '16 at 14:03

score 1 · Accepted Answer · answered Sep 19 '16 at 13:46

1

If you want to avoid the clipboard may I suggest R1C1 formula format:

Sub fillsheet()
Dim ows As Worksheet
Dim tws As Worksheet
Dim rng As Range

Set ows = Worksheets("Sheet1")
Set tws = Worksheets("Sheet2")

Set rng = ows.UsedRange

tws.Range(rng.Address()).FormulaR1C1 = "='" & ows.Name & "'!RC"
End Sub

answered Sep 19 '16 at 13:46

Scott Craner

148,073
10
49
81

Thanks again Scott. I have chosen this one as I needed to use variables for the Worksheets, but @iDevlop's solution was great and so simple too – Dave Sep 19 '16 at 14:18

Macro in Excel to Copy a Worksheet (by referencing every cell)

2 Answers2

Linked