I have the code below as part of a spreadsheet that automatically generates and invoice. It has worked fine until today. It place the word "done" in the completed column when executed. This allows for only one invoice at a time to be generated.
When I used it today, the code is not recognizing the word "done" and continues to try to generate and invoice for a line in the spreadsheet that has no data entered.
Code is below. I am using Windows 10 Microsoft Office 2016.
Excel workbook containing the worksheet and the invoice template file are all contained in
H:\Documents\General Files\Drill\Lime Spreader Invoices\2018
Any assistance appreciated. I cannot determine what has caused the code to start working improperly.
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 = 5 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, 14).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")
.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("E20").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
Bookmarks