Macro in Excel to Copy a Worksheet (by referencing

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"

Answer 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

Answer 2

You nearly gave the answer in your question:

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