For sheets, there are (as you know) at least three ways of reference.
Indexing by .Name Sheets("SheetName") which indexes by the name on the sheet tab.
Indexing by number Sheets(1) which indexes by the sequence of sheet tabs.
Using the CodeName found in Project Explorer, which will be the same as .name, unless a user (Sheet tab) or a programmer (CodeName) changes something.
Moving the sheets around will disturb the numerical indexing of sheets, so unless you put replacement sheet in the same location as the previous sheet, the numerical index will change.
If you insert the new sheet before the 'old' sheet, and then delete, the same numerical index will work. And after the delete, the numerical index will refer to the new sheet.