Excel macro VBA new workbook with one named sheet

Question

I need a macro to able to create a new workbook with only one sheet named "Options" and the following worksheets must start with "Sheet1" not with "Sheet2". Is it possible?

Answer 1

The code below will add a new Workbook with just 1 sheet named "Options".

However, regarding the second part, in order to start from "Sheet1" instead of "Sheet2" it involves adding code to the new created Workbook, since by default the new workbook already has 1 worksheet named "Options", so in fact you are adding a 2nd worksheet, so Excel automatically names it "Sheet2"

Option Explicit

Sub CreateNewWB()

With Application
    .SheetsInNewWorkbook = 1
    .Workbooks.Add
    .Sheets(1).Name = "Options"
End With

End Sub

Answer 2

Think this is what you're looking for

This first creates a new workbook, then adds a new sheet at the beginning before renaming it to "Options". The rest of the sheets should then follow the default naming pattern

Option Explicit
Public Sub NewWorkbook()
    With Workbooks.Add
        With .Sheets.Add(Before:=.Sheets(1))
            .Name = "Options"
        End With
    End With
End Sub