Copying a sheet, but preserving the formulas

[Guest]

Hello,

I have a "template" worksheet ACTUALS within a workbook called TEMPLATE.xls.
I need to copy the ACTUALS worksheet to other workbooks. The code executes
OK, but the formulas in the destination worksheet are changed to point to the
Template source workbook. I tried to do this manually, and I get the same
results (Destination formulas point back to the TEMPLATE.XLS workbook.

How can I copy it so that it doesn't point to the source workbook?

Many Thanks.

 

[Dave Peterson]

You could do your copy, then edit|links and change those links.

another alternative that I like is to change all the formulas to text
copy your stuff
paste your stuff
change all the text formulas back to formula formulas.
(in both worksheets!)

Option Explicit
Sub testme()

Dim WksToCopy As Worksheet
Dim WksToPaste As Worksheet

Set WksToCopy = Workbooks("book1.xls").Worksheets("sheet1")
Set WksToPaste = Workbooks("book2.xls").Worksheets("sheet2")

With WksToCopy
.Cells.Replace what:="=", replacement:="$$$$$$"
.Cells.Copy _
Destination:=WksToPaste.Range("a1")
.Cells.Replace what:="$$$$$$", replacement:="="
End With
WksToPaste.Cells.Replace what:="$$$$$$", replacement:="="

End Sub

Use some unique string that doesn't appear in your workbook!