Copy sheet while updating the cell formula references

3
votes

I have 5 sheets in a workbook. When I "manually" copy 2 of them (in the same workbook), the references (in the cell formulas) are changed automatically for the new copied sheets. For example in some cell I have the following formula:

=IF('1_Result'!B5="";NA();'1_Result'!B5*$C$3)

And after I copy the new sheets, the formulas in the new sheets changes to:

=IF('1_Result (2)'!B5="";NA();'1_Result (2)'!B5*$C$3)

Where "1_Result" is the original sheet and "1_Result (2)" is the copied sheet. When I do the copying with the following code:

t1.Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = nshn & "_Table"

...the formulas in the copied sheet don't change the references, so it stays the same like:

=IF('1_Result'!B5="";NA();'1_Result'!B5*$C$3)

How should I modify the code, so that the references are changed?

3
vbaexcel
+1 for interesting question. It does appear that the Copy method does not do the same thing as manually copying the sheets. - David Zemens
what do you mean by I "manualy" copy 2 of them? Copy at the same time? If yes, try to do the same in VBA: Sheets(Array("Sheet1", "1_Result")).Copy After:=Sheets(Sheets.Count) - it works for me: Sheet1 initially has formula ='1_Result'!B5 and after copying thouse sheets Sheet1 (2) has formula ='1_Result (2)'!B5. Let me know if it helps - Dmitry Pavliv
I believe you're right. The reason the references aren't updated is, because I copy them one by one, not both at once. I will take a look at it now. - Trenera

3 Answers

2
votes

As I mentioned in comments, if you want references to be updated, copy two sheets at the same time:

Sheets(Array("Sheet1", "1_Result")).Copy After:=Sheets(Sheets.Count)

It works for me:

Sheet1 initially has formula ='1_Result'!B5 and after copying thouse sheets Sheet1 (2) has formula ='1_Result (2)'!B5

0
votes

As simco noticed, I didn't copied the sheets together at once, but one by one. The following code solves the problem:

Sheets(Array("1_Table", "1_Result")).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count - 1).Name = nshn & "_Table"
Sheets(Sheets.Count).Name = nshn & "_Result"
0
votes

While trying to figure out what is wrong, I found an alternative solution which is definitely not recommended to use, but might be helpful to someone. I used a function that replaces a string with another in a single sheet. Here's the code:

Function FormulaFindAndReplace(sh As Worksheet, phrase As String, replacement As String)
With sh
    Set Found_Link = Cells.Find(what:=phrase, After:=ActiveCell, _
        LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
        searchdirection:=xlNext, MatchCase:=False)
    While UCase(TypeName(Found_Link)) <> UCase("Nothing")
       Found_Link.Activate
       Found_Link.Formula = Replace(Found_Link.Formula, phrase, replacement)
       Set Found_Link = Cells.FindNext(After:=ActiveCell)
    Wend
End With
End Function