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:
Code: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




Reply With Quote
Bookmarks