Object Not Set Help

[eknight74]

I have copied a file I previously used without problems and changed the path and location of files. Now I get an error message telling me a particular line has an error of object not set or with block object not set. I cannot figure out how to correct the error. It looks as thought my code is correct. I have posted the code below. It is for VBA in the excel application. Thanks in advance for any assistance that can be given.

Option Explicit

Function LastRow(ByVal ws As Worksheet, Optional ByVal col As Variant = 1) As Long
With ws
LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
End Function

Private Sub CommandButton1_Click()

Const path$ = "H:\Documents\General Files\Drill\Lime Spreader Invoices\2018\"

Dim name As String, address As String, invoicenumber As Long
Dim r As Long, mydate As String, myfilename As String, wbInv As Workbook
Dim endmeterread, beginmeterread, zip, city, pickupdate, returndate, numberofdays, firstdayrate
Dim tempWB As Workbook
Dim wsCD As Worksheet

Set wsCD = ThisWorkbook.Worksheets("RentalDetails")

For r = 4 To LastRow(wsCD)

If Not Cells(r, 18).Value = "done" Then

With wsCD

name = .Cells(r, 4).Value
address = .Cells(r, 15).Value
invoicenumber = .Cells(r, 1).Value
endmeterread = .Cells(r, 6).Value
beginmeterread = .Cells(r, 5).Value
zip = .Cells(r, 17).Value
city = .Cells(r, 16).Value
pickupdate = .Cells(r, 2).Value
returndate = .Cells(r, 3).Value
numberofdays = .Cells(r, 12).Value
firstdayrate = .Cells(r, 8).Value
.Cells(r, 18).Value = "done"
End With

Application.DisplayAlerts = False
Set wbInv = Workbooks.Open("H:\Documents\General Files\Drill\Lime Spreader Invoices\2018\Invoice.xlsx")

With wbInv.Worksheets("Invoice") This line is where the error occurs.
.Range("G11").Value = invoicenumber
.Range("A14").Value = name
.Range("A15").Value = address
.Range("A16").Value = city
.Range("D16").Value = zip
.Range("E19").Value = endmeterread
.Range("E21").Value = beginmeterread
.Range("G8").Value = pickupdate
.Range("I8").Value = returndate
.Range("E25").Value = numberofdays
.Range("E27").Value = firstdayrate

End With

mydate = Format(Date, "mm_dd_yyyy")
wbInv.SaveAs Filename:=path & invoicenumber & " - " & name & _
" - " & mydate & ".xlsx"
myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True
'ActiveWorkbook.PrintOut copies:=1
wbInv.Close SaveChanges:=False
Set wbInv = Nothing
Set tempWB = Nothing

End If

Next r

End Sub

[Zvoni]

And which particular line would that be?
...and use Code-Tags

EDIT: Sorry, just saw it.
Question: Do you have a Worksheet called "Invoice" in that Workbook?

[eknight74]

I have a workbook called limespreaderreport.xlsm and a separate worksheet called invoices.xlsx They are separate files but contained in the same folder. The folder path is Documents\General Files\Drill\Lime Spreader Invoices\2018\

I have the exact same set-up for another piece of equipment and it works fine without any errors. I changed a few cell locations and the path and the result was the error message. Hope this offers the info you need. Thanks so much.

[jdc2000]

Note:
Invoice.xlsx <> invoices.xlsx

[eknight74]

Thank you.

[Zvoni]

Something must be wrong with this line, otherwise wbInv would be a valid object

Set wbInv = Workbooks.Open("H:\Documents\General Files\Drill\Lime Spreader Invoices\2018\Invoice.xlsx")

Either that, or something's wrong with Worksheet.

Have you tried accessing the Sheet through index (wbInv.Sheets(0))?

[vbfbryce]

The Worksheet is 1 based, not zero, so that would generate an error.

[Zvoni]

The Worksheet is 1 based, not zero, so that would generate an error.

bryce, you're right.
I was writing that yesterday evening from my bed while watching NCIS

[vbfbryce]

LOL, I'm often guilty of similar distractions, but usually it would be Criminal Minds.

[eknight74]

His has been resolved. Thanks.

[Zvoni]

What was it?
It's good behaviour to tell the solution and/or what the mistake was, so others might benefit from it

[westconn1]

What was it?

see post #5 and 4

[Zvoni]

You gotta be kidding me!
Since he said, that he has the same setup just with different paths i wasn't checking the filename itself.
GNARF!!