Hi Michael,

I saw your post about 3 minutes after you posted but I was on my phone about to go to sleep so apologies for the delayed response! Good news is I've done what you're asking. Here is the code I used. I have 8 Queries and running this code outputs all 8 queries into separate tabs within the 1 workbook, which I believe is what you want.

    DoCmd.TransferSpreadsheet acExport, , "LeadSources1CIMSWrittenSumQ", "C:\DB\Reports\LeadSourcesReport.xlsx"
    DoCmd.TransferSpreadsheet acExport, , "LeadSources2CIMSWrittenCountQ", "C:\DB\Reports\LeadSourcesReport.xlsx"
    DoCmd.TransferSpreadsheet acExport, , "LeadSources3CIMSIssuedSumQ", "C:\DB\Reports\LeadSourcesReport.xlsx"
    DoCmd.TransferSpreadsheet acExport, , "LeadSources4CIMSIssuedCountQ", "C:\DB\Reports\LeadSourcesReport.xlsx"
    DoCmd.TransferSpreadsheet acExport, , "LeadSources5HLPPWrittenSumQ", "C:\DB\Reports\LeadSourcesReport.xlsx"
    DoCmd.TransferSpreadsheet acExport, , "LeadSources6HLPPWrittenCountQ", "C:\DB\Reports\LeadSourcesReport.xlsx"
    DoCmd.TransferSpreadsheet acExport, , "LeadSources7HLPPIssuedSumQ", "C:\DB\Reports\LeadSourcesReport.xlsx"
    DoCmd.TransferSpreadsheet acExport, , "LeadSources8HLPPIssuedCountQ", "C:\DB\Reports\LeadSourcesReport.xlsx"

If MsgBox("Report Updated Successfully. Open?", vbYesNo + vbQuestion, "Lead Sources Report") = vbYes Then
Dim Filename As String
Filename = "C:\DB\Reports\LeadSourcesReport.xlsx"
Application.FollowHyperlink Filename
End If


To pull this apart:

DoCmd.TransferSpreadsheet acExport, , "LeadSources1CIMSWrittenSumQ", "C:\DB\Reports\LeadSourcesReport.xlsx"
I prefer DoCmd.TransferSpreadsheet as opposed to other methods since its more specific and more modern (Can export to XLSX). Specify the Query to export. Specify the spreadsheet to export to (Ensure it's the same spreadsheet and check for typos!)

If MsgBox("Report Updated Successfully. Open?", vbYesNo + vbQuestion, "Lead Sources Report") = vbYes Then
Dim Filename As String
Filename = "C:\DB\Reports\LeadSourcesReport.xlsx"
Application.FollowHyperlink Filename
End IF
This code is quite archaic. It assumes the operation will always be successful with no error handling but this line of code and the following provides a yes/no box to open the resulting spreadsheet

Ultimately depends on how far you want to go but this will definitely get you started

Thanks Dan and no worries about delays.  I am always very appreciative of the help the people here provide.

So I am not using the transferspreadhseet method for the first query.  Instead, based on one of the videos and information here I am using the code to create the recordset, set the application to excel, etc and I am using code to format the sheet (column widths, wrapping text format, formating as a table etc.) which I don't think you can do using the transferspreadsheet method.  Here is my current code;

code

Dim DayNumber                       '****************
    Dim MonthNum                        '** Used to    **
    Dim MonthNam                        '** build out  **
    Dim YearNumber                      '** Excel name **
    Dim NewDateFormat As String         '** using date **
    Dim ExcName As String
    Dim Filepth As String               '****************
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlSheetClose As Excel.Worksheet
    Dim SQL As String                   '**Open Records**
    Dim RSL As DAO.Recordset            '**Open Records**
    Dim RNG As Range                    '**Open Records**
    Dim TBL As ListObject
  
    

    DoCmd.Hourglass True
    
'*** Create Date format to be use in file Title ***
    DayNumber = Day(Date)
    MonthNum = Month(Date)
    MonthNam = MonthName(MonthNum, True)
    YearNumber = Year(Date)
    NewDateFormat = DayNumber & "-" & MonthNam & "-" & YearNumber
    ExcName = "Operations Tracker - " & NewDateFormat
    Filepth = "C:\Users\medicim\OneDrive - Bristol Myers Squibb\Documents\Operations Sheets\" & ExcName
      
'***SQL to retrieve data for Open Records***
    SQL = "Select * FROM qryquotewnoteforexport"
    Set RSL = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
    
    If RSL.RecordCount = 0 Then
        MsgBox "No Data selected for export", vbInformation + vbOKOnly
        GoTo SubExit
    End If
    
'***Create worksheet 1
    Set xlApp = Excel.Application
    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)
    xlSheet.Name = "Open Projects"
    
    With xlSheet
        .Columns("A").ColumnWidth = 8   'Quote
        .Columns("B").ColumnWidth = 8   'Entity
        .Columns("C").ColumnWidth = 8   'type
        .Columns("D").ColumnWidth = 40  'Name
        .Columns("E").ColumnWidth = 10  'Status
        .Columns("F").ColumnWidth = 10  'Region
        .Columns("G").ColumnWidth = 10  'Sitecode
        .Columns("h").ColumnWidth = 10  'Previous Date
        .Columns("i").ColumnWidth = 50  'Previous Note
        .Columns("J").ColumnWidth = 50  'New Note
        .Columns("D").WrapText = True   'wrap text for name
        .Columns("I").WrapText = True   'wrap text for Note
        .Columns("J").WrapText = True   'Wrap text for new note
        .Columns("A").VerticalAlignment = xlTop
        .Columns("B").VerticalAlignment = xlTop
        .Columns("C").VerticalAlignment = xlTop
        .Columns("D").VerticalAlignment = xlTop
        .Columns("E").VerticalAlignment = xlTop
        .Columns("F").VerticalAlignment = xlTop
        .Columns("G").VerticalAlignment = xlTop
        .Columns("H").VerticalAlignment = xlTop
        .Columns("I").VerticalAlignment = xlTop
        .Columns("J").VerticalAlignment = xlTop
        '.Columns("H").NumberFormat -"dd-mmm-yyyy"
        .Range("J1").Value = "New Note"
        
        For cols = 0 To RSL.Fields.Count - 1
            .Cells(1, cols + 1).Value = RSL.Fields(cols).Name
        Next
            '*** Add filter dropdown to sheet ***
            With .Range("a1:I1")
                .Font.Bold = True
                .AutoFilter Field:=1, visibledropdown:=True
            End With
         'freeze worksheet at E2
         xlSheet.Application.ActiveWindow.FreezePanes = False
         .Range("e2").Select
         xlSheet.Application.ActiveWindow.FreezePanes = True
        
         .Range("A2").CopyFromRecordset RSL
      
    End With

    '***Format sheet as table style medium 2 ***
    Set RNG = xlSheet.Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell))
    Set TBL = xlSheet.ListObjects.Add(xlSrcRange, RNG, , xlYes)
    TBL.TableStyle = "tablestylemedium2"
  
    xlBook.SaveAs FileName:=Filepth 'Saves workbook
    
SubExit:
    On Error Resume Next
    DoCmd.Hourglass False
    xlApp.Visible = True
    RSL.Close
    Set RSL = Nothing
Exit Sub

Then you are way beyond me! Would love to see this in a tech help video! ahem

Lol.  I think the original code I got from one of the videos.  This code works really great when you want to format the sheet so it is presentable and deliverable to a client without further manipulation.

Yeah I've got some cool Automation videos coming up.

Great Richard.  Your videos have help me tremendously.  Keep'em coming.  If there is anything you can provide for this topic, that would be great.