Trying to create automated invoicing Excel VBA

Question

I am trying to create automated invoices from a list of customer data in excel. I have experience with Macros and some VBA but nothing this in depth before. I copy the basic template for what I wanted here. I copied most of the structure and changed what I needed, but its not working for me. I tried to work through it on my own and research other methods but I am afraid I'm in over my head now. Here is my code, I'm getting a 'Run Time Error 1004'. If someone could show me what is needed to fix it I would greatly appreciate it.

Code:

Private Sub CommandButton1_Click()

Dim customer As String
Dim customerid As String
Dim invoicenumber As Long
Dim r As Integer
Dim mydate As String
Dim path As String
Dim myfilename As String
Dim providercount As Integer
Dim basefee As Integer
Dim faxlines As Integer
Dim faxpages As Integer
Dim faxbundles As Integer
Dim invoicedate As String
Dim lastrow As Long

'This row is causing the error 
lastrow = Sheets("Greenway").Range(“A” & Rows.Count).End(xlUp).Row


r = 8


For r = 8 To lastrow


If Cells(r, 14).Value = “done” Then GoTo nextrow


customer = ThisWorkbook.Sheets(“Greenway”).Cells(r, 3).Value
customerid = ThisWorkbook.Sheets(“Greenway”).Cells(r, 2).Value
providercount = ThisWorkbook.Sheets(“Greenway”).Cells(r, 5).Value
basefee = ThisWorkbook.Sheets(“Greenway”).Cells(r, 6).Value
faxlines = ThisWorkbook.Sheets(“Greenway”).Cells(r, 7).Value
faxpages = ThisWorkbook.Sheets(“Greenway”).Cells(r, 10).Value
faxbundles = ThisWorkbook.Sheets(“Greenway”).Cells(r, 11).Value
invoicenumber = ThisWorkbook.Sheets(“Greenway”).Cells(r, 15).Value
invoicedate = ThisWorkbook.Sheets(“Greenway”).Cells(r, 16).Value

Cells(r, 14).Value = “done”
Application.DisplayAlerts = False
Workbooks.Open ("C:\Users\Andrew\Dropbox (Updox)\All Company\DEPT-Finance\Billing\Greenway\Invoices\2015Invoices\Template.xlsx")
ActiveWorkbook.Sheets(“invoice”).Activate

ActiveWorkbook.Sheets("Invoice").Range("E7").Value = customername
ActiveWorkbook.Sheets("Invoice").Range("E5").Value = invoicenumber
ActiveWorkbook.Sheets("Invoice").Range("A16").Value = providercount
ActiveWorkbook.Sheets("Invoice").Range("A17").Value = faxlines
ActiveWorkbook.Sheets("Invoice").Range("A18").Value = faxpages
ActiveWorkbook.Sheets("Invoice").Range("A19").Value = faxbundles
ActiveWorkbook.Sheets("Invoice").Range("E8").Value = customerid
ActiveWorkbook.Sheets("Invoice").Range("D16").Value = basefee
ActiveWorkbook.Sheets(“invoice”).Range("E4").Value = invoicedate

path = "C:\Users\Andrew\Dropbox (Updox)\All Company\DEPT-Finance\Billing\Greenway\Invoices\2015Invoices\"
mydate = Date
mydate = Format(mydate, “mm.yy”)

ActiveWorkbook.SaveAs Filename:=path & “_” & customername & “_” & mydate & “.xlsx”
myfilename = ActiveWorkbook.FullName
SetAttr myfilename, vbReadOnly
Application.DisplayAlerts = True
ActiveWorkbook.PrintOut copies:=1

ActiveWorkbook.Close SaveChanges:=False

nextrow:

Next r


End Sub    

Answer 1

Maybe you should try this, to find the last Row in Col A, and solve the 'run time error 1004':

With Sheets("Greenway")
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

or:

lastrow = Sheets("Greenway").Range("A" & Sheets("Greenway").Rows.Count).End(xlUp).Row

instead of:

lastrow = Sheets("Greenway").Range(“A” & Rows.Count).End(xlUp).Row

Notice the dot (.) separator before Rows to indicate that you want to get the property Rows OF Sheets("Greenway") object.

---

For the error you are getting now 'run time error 9', try:

customer = Sheets("Greenway").Cells(r, 3).Value

instead of:

customer = ThisWorkbook.Sheets(“Greenway”).Cells(r, 3).Value

Hope it's useful!