How to reset the sheets number?

If Sheet1 does not exist then when the workbook is closed and re-opened then adding a worksheet it should be Sheet1. This in not always convenient or even possible if running via VBA code within the workbook.

If it is simply the tab name then you can change the tab name at time of creating the worksheet in VBA like the following. Note that it is necessary to test if the worksheet already exists because if it does then the code will error when attempting to rename.

Note that if you are creating a temporary worksheet and deleting it from within a VBA code Sub then if created by assigning it to a variable like in the example then you can reference the worksheet by the variable name without ever knowing what the name of the sheet is.  You can even delete it by the variable.

Sub Macro1()

    Dim wsNew As Worksheet

    Set wsNew = Nothing

    On Error Resume Next

    Set wsNew = Worksheets("Sheet1")

    On Error GoTo 0

    If wsNew Is Nothing Then

        Set wsNew = Sheets.Add(Before:=Sheets(1))

        wsNew.Name = "Sheet1"

    Else

        MsgBox wsNew.Name & " already exists."

        Exit Sub

    End If

End Sub

If it is the CodeName that you want to change then see the code in the link below. (The code name is the name that you see in the Project Explorer on the left hand side in the VBA editor. The Code name is the one NOT in parenthesis and the tab name given by the user is the name in parenthesis.)

It is often preferable to use the code name because it is somewhat more difficult for the end user to change it.

If you use the code example at the following link then you will need to select Tools menu  and then References and scroll down to Microsoft Visual basic for applications extensibility 5.3 and check the box. (Ensure you check the box; not highlight the line) and then click OK.

http://www.tek-tips.com/faqs.cfm?fid=4090