References lost: extrenal and internal sheet names

[roberto21]

For a number of reasons, my code needs to
1) duplicate a sheet (sheet1)
2) work on duplicated sheet (sheet1 (2))
3) at the end, delete original sheet1 and rename the duplicated sheet to sheet1
Unfortunately, there is a third sheet with fixed references to cells in sheet1 (with fixed I mean that the references are to cells not interested in the work done in point 2), and these references disappear (#ref).
Trying to find a solution, I discovered that the sheets have an internal name (codename) in addition to the external name that appears on the tabs. So I thought that maybe the references need that name as well. Therefore, I modified my code (see below) to save the original sheet1 codename and restore it to the "new" renamed sheet1. Even if the code seems to work (sheet1 gets indeed the old codename) the references still disappear. Is it really impossible to restore the references to a deleted sheet, after reconstructing it?
Thanks
Robert



Sub Macro1()
'
' Macro1 Macro
'
Dim LastRow, I, Index As Long
Dim ActualCodename, ActualCodename2 As String

'create a copy of Banca
Sheets("Banca 2015").Select
' get the codename for "Banca 2015" to be used later
ActualCodename = Sheets("Banca 2015").CodeName
Application.DisplayAlerts = False
Sheets("Banca 2015").Copy Before:=Sheets(3)

Application.DisplayAlerts = True

' working code follows...

'***************************************+


' delete old sheet and rename new one

Application.DisplayAlerts = False
Sheets("Banca 2015").Delete
Application.DisplayAlerts = True

'Rename sheet (EXTERNAL name)
Sheets("Banca 2015 (2)").Name = "Banca 2015"
'get codename of this renamed sheet
ActualCodename2 = Sheets("Banca 2015").CodeName
' restore original codename saved at the beginning...
With ActiveWorkbook
.VBProject.VBComponents(ActualCodename2).Propertie s("_CodeName").Value = ActualCodename
End With

End Sub

[6StringJazzer]

No need to mess with the codename for this problem. The strategy is to:

Make the copy
Rename the copy to something that doesn't include the original full name (otherwise it will mess up updating the formulas)
Update the formulas to point to the new sheet
Delete the original sheet
Rename the new sheet, and Excel will automatically update the formulas to the updated name

This code is tested:


Sub Macro1()

Dim ws As Worksheet

'create a copy of Banca
Application.DisplayAlerts = False
Sheets("Banca 2015").Copy Before:=Sheets(3)
Application.DisplayAlerts = True
Sheets("Banca 2015 (2)").Name = "New Banca"

' Update formulas to point to new sheet
For Each ws In Worksheets
ws.Cells.Replace What:="Banca 2015", _
Replacement:="New Banca", _
LookAt:=xlPart
Next ws

' delete old sheet
Application.DisplayAlerts = False
Sheets("Banca 2015").Delete
Application.DisplayAlerts = True

'Rename new sheet, Excel will automatically update references to it in formulas
Sheets("New Banca").Name = "Banca 2015"

End Sub