Access, Excel 2010 - Copy Worksheet to End of Book (run-time error '91'

[Garr]

First, thanks for reading. Allow me to set up the project concept.

I have an Access database that stores athletic contest officials information. To pay the officials, I have to use an Excel cover sheet created by my company. I've managed a way to get Access to open the worksheet and drop the data from the current record on my form, but I'd like to have a one-button press to generate ALL of my cover sheets on their own tabs in a single Excel Workbook.

Objectives:
Open the workbook
do until EOF

Copy the sole sheet at the end of all the sheets
Rename the sheet based on data in the current record
Copy the current record's data onto the new worksheet
​

next record

Here's my current code. It is *not* cycling through the recordset yet because I can't even get it to work correctly for a single record.

Private Sub cmdGenerateDIVs_Click()

    'objects
    Dim objXLApp As Excel.Application
    Dim objXLBook As Excel.Workbook
    Dim objWSTemplate As Excel.Worksheet
    Dim objWS As Excel.Worksheet
    
    'variables
    Dim strRemitAdvice As String
    Dim strDescription As String
    Dim strInvoiceNo As String
    Dim strSheetName As String
    
    'object setup
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open("O:\ATH_BIZ\2012-2013\01 Men's Sports\MVB - Men's Volleyball\OfficialsContracts\MVBOfficialsDIVs.xlsx")
    Set objWSTemplate = objXLBook.Sheets(1)
      
    objXLApp.Application.Visible = True
    
    'saving the document as a test document
    objXLBook.SaveAs "O:\ATH_BIZ\2012-2013\01 Men's Sports\MVB - Men's Volleyball\OfficialsContracts\" & Me.OfficialLast & " " & Me.OfficialFirst & " TEST"
    
    strRemitAdvice = "*IPFW vs " & Me.AwayTeam_TeamAbbreviation & " " & Me.MatchDate & " " & Me.OfficialTypeAbbr
    strDescription = "Men's Volleyball Official " & strRemitAdvice
    strInvoiceNo = "SRVC" & Format(Month(Me.MatchDate), "00") & Format(Day(Me.MatchDate), "00") & Format(Year(Me.MatchDate), "YYYY")
    strSheetName = Me.OfficialLast & Format(Month(Me.MatchDate), "00") & Format(Day(Me.MatchDate), "00")
    
    'creating a copy of the main DIV and renaming it
    objWS = objXLBook.Worksheets(objWSTemplate.Name).Copy(After:=objXLBook.Sheets(Worksheets.Count))
    objWS.Name = "Test"
        
    'placing the data from the form into the excel spreadsheet
    With objXLApp.ActiveSheet
        .Range("D2").Value = Me.VendorNumber
        .Range("F1").Value = Me.TIN
        .Range("A4").Value = Me.AddrName
        .Range("A5").Value = Me.Address
        .Range("A6").Value = Me.CSZ
        .Range("A10").Value = strRemitAdvice
        .Range("D13").Value = Me.OfficialTypePay
        .Range("D14").Value = Me.RoundTrip
        .Range("D15").Value = Me.Mileage
        .Range("B18").Value = strDescription
        .Range("F35").Value = Date
        .Range("G16").Value = strInvoiceNo
    End With

End Sub

When I run that code, it copies the sheet to the end, but cannot move forward. I get a run-time error, and I can't figure out why.

Run-Time error '91': Object variable or With block variable not set

I've searched for about two hours trying to make this work, and I can't find anything that helps. Can anyone assist? What further do you need from me?

 

[Garr]

I learned lots today. This is my first time tackling something like this, so I really appreciate all the guidance.

 

[strive4peace]

you're welcome, Garr. btw, lots of code you write in Excel can also be used in Access (and vice-verse) since they both use VBA ... think about that as you write functions to calculate this or that in Excel, and use Option Explicit and error handlers ... they work the same