Excel VBA renames the wrong sheet

I have an excel macro-enabled workbook that has 5 sheets. One of these sheets is named "NC" and one is named "SC". I have part of the code which adds dates to these sheets' names.

Sheets("NC").Name = "NC" & Replace(Date, "/", "-") Sheets("SC").Name = "SC" & Replace(Date, "/", "-")

Then later on if the reset button is clicked, I made a code to switch them back to previous "NC" and "SC" names but this is where I get the problem.

Sheets(2).Name = "NC" Sheets(3).Name = "SC"

Sheets(2).Name = "NC" works fine Sheets(3).Name = "SC" however, renames Sheet(4) instead

I thought these sheet codes or sheet numbers don't change no matter how you rearrange or reorder your sheet tabs. I don't understand why it accurately renamed Sheets(2) but not Sheets(3). Look at my project explorer below, it renames Sheets(4) instead from "NCToday" to "SC". Sheets(3) is showing "SC" in the picture but because this was manually reset but you can see the arrangement, the code Sheets(3).Name = "SC" SHOULD NOT have renamed Sheets(4) from "NCToday" to "SC".

Sheet List

Solution

Sheet(3) does not necessarily mean sheet3

Example: enter image description here

If I type into the immediate window, ?worksheets(1).name, I get this result:

Sheet2

note that the system looks at the sheet order on the excel spreadsheet (Sheet2,Sheet1, Sheet3), not the order in the VBA Project (Sheet1,Sheet2,Sheet3)

To rename the sheets back, you may want to reverse the method you used to rename them initially:

Sheets("SC" & Replace(Date, "/", "-")).Name = "SC"