I recently had the need to create workbooks and worksheets from scratch so this is what I did. No error trapping was added in order to keep the example simple!

Public Sub CreateExcelWorkBookAndSheetsTest()

    Dim xlsNew As Excel.Application
    Dim wkbNew As Excel.Workbook
    Dim wksNew As Excel.Worksheet
    Dim strNewWorkBook As String
    Dim strWorkBookName As String
    
    ' Create the New Workbook Name
    strWorkBookName = "TEST EXCEL FROM MSACCESS VBA"
    strNewWorkBook = CurrentProject.Path & "\" & strWorkBookName & ".xlsx"
        
    If Dir(strNewWorkBook) <> "" Then
        Kill strNewWorkBook
    End If
    
    ' Create a New Excel File and add the WorkSheet to it. Then add the WorkSheet.
    Set xlsNew = New Excel.Application
    
    ' New Workbook
    Set wkbNew = xlsNew.Workbooks.Add
    
    ' Create Workbook and name the first Worksheet Index. By default it will be Sheet1
    ' Reference Worksheets(1) because this is the first worksheet that is created with the initial workbook!
    Set wksNew = wkbNew.Worksheets(1)
    wksNew.Name = "Index"
    ' Use SaveAs only once at the beginning!
    wkbNew.SaveAs strNewWorkBook, xlOpenXMLWorkbook
    
    ' Add another Worksheet and name it Main Data 1. Create a new reference to the worksheet using Count!
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    ' Add the worksheet to the workbook "After" the wksNew reference that we created above.
    wkbNew.Sheets.Add After:=wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count).Name = "Main Data 1"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save
    
    ' Add another Worksheet and name it Main Data 2. Same comments as above.
    ' You can use After:= to reference the [After] parameter or you can just used the parameter like below.
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    wkbNew.Sheets.Add , wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count).Name = "Main Data 2"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save
    
    ' Add a Final Worksheet call Total
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    wkbNew.Sheets.Add , wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count).Name = "Total"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save

    ' Now you can create another worksheet and put it before or after another one!
    Set wksNew = wkbNew.Worksheets(wkbNew.Sheets.Count)
    wkbNew.Sheets.Add Before:=wksNew
    ' We use the -1 in this case as we want to make sure we Name the sheet before the reference "Before" using wksNew
    wkbNew.Sheets(wkbNew.Sheets.Count - 1).Name = "Final Test"
    ' Just need to use Save now as we have already saved it once.
    wkbNew.Save

    ' Now we just add 5 new sheets! These are added starting at the beginning of the last Sheet (workbook) we added, Final Test
    wkbNew.Sheets.Add Count:=5

    Set xlsNew = Nothing

    Application.FollowHyperlink strNewWorkBook
End Sub