That will work, but you need to be careful
Each sheet has 2 Names, The name on the Sheet Tab(Which is what you are changing with this code) and The Index Name. In a new workbook, you will have a sheet with Index Name and Sheet Name both "Sheet1". You can, using the code, change another sheet's Tab Name to "Sheet1", provided you have renamed the original "Sheet1"
So you could have 2 sheets, one with an Index Name of "Sheet1" and a Tab Name of "Sheet2" while the other might have an Index Name of 'Sheet2" and a tab name of "Sheet1"
I actually saw Bill (AKA MrExcel use this once).
The only reason I mention this is because it could become very confusing if you are trying to write VBA for the workbook