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!!!!

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!!!!

Last edited: