Formulas/VBA: .Replace after .Copy also edits source formulas

[Will_B]

Hi. My problem concerns copying and pasting formulas using VBA, then using .Replace to edit the formula in the destination cells. The problem I have is that the .Replace also changes the formula in the source cells. I want only the formulas in the destination cells to be edited. The code below is a simplified version of what I want to do, but it produces the same error. I am using Excel 2007.

To replicate this, put these formulas in cells A1 and A2 of Sheet1:
=1+2
=2+2
... then run the code below. You'll see that the original formulas on Sheet1 are also edited.

How do I keep the formulas on Sheet1 from being edited?
-------------------------------------------------------
Sub Test()
Dim wksCopyFrom As Worksheet
Dim wksDestination As Worksheet
Dim rngCopyFrom As Range
Dim rngDestination As Range

'Worksheet that has the formula I want to copy...
Set wksCopyFrom = Worksheets("Sheet1")
'Where the formula (originally) is "=1+2"...
Set rngCopyFrom = wksCopyFrom.Range("A1:A2")
'Worksheet where I want to copy the formula
Set wksDestination = Worksheets("Sheet2")
'Where I want to paste the formula, then edit it with find-and-replace...
Set rngDestination = wksDestination.Range("A1:A2")

'Copy the formula
rngCopyFrom.Copy
'Paste it
rngDestination.PasteSpecial
'Turn off copy mode
Application.CutCopyMode = False

'In the destination cells, CHANGE "2" to be "9" in the formulas
rngDestination.Cells.Replace _
What:=2, Replacement:=9, _
SearchOrder:=xlByColumns

End Sub
-------------------------------------------------
NOTE: In my real-world situation, what I am copying are various large tables that vary in size and include formulas, fixed text, conditional formatting, borders, etc. I have to copy it N times and edit each instance to refer to a different worksheet (of which there are N many). Therefore my final code must capture all text, formatting, etc., to be copied, too. This is why I haven't tried to simply copy the formulas as text. Hopefully my example above does not oversimplify the situation. Thanks for considering my problem!!!!

 

[pgc01]

Hi Will

I just tried your code and it works ok. The source cells did not change. Can you try it following the instructions you posted?

 

[Will_B]

It works for me this morning, too. Sheesh. Yesterday I tested it three times before pasting the code into this posting. Each time the problem occured. Apparently it was a JOOTT because my more complex code I'm working on also works fine this morning. Perhaps this is a bug in Excel 2007, that it needs a night's rest to run VBA? I swear it wasn't programmer fatigue. Thanks for taking the time to run this and to respond so quickly. If nothing else, this posting will let others know it is just a temporary JOOTT.