Generating Invoices from Excel Sheet

[REUBEN]

Hi All,

I've used a VBA code to create invoices for my casework from my worksheet. This code was provided online and I have tweaked it to work for my specific requirements. But I do know have the VBA programming skills to figure out how to make it do a few specific tasks.

The code picks up certain data from my work sheet and uses a template to print a softcopy of an invoice. I want to tweak it so I could embed the button in each row so it would generate invoices for the data on that specific row only. Right now the code is made so it would generate invoices endlessly from a specified starting row to an ending row (which includes blank rows too - which is totally unnecessary). There is a step in the code that checks if that row's invoice has been prepare or not and then is to generate its respective invoice. But I dont want that, I'd rather have the button be embedded in each respective row and make it generate the invoice for that specific row only. Could anyone help me correct this please?

Sub Button5_Click()​

​

Dim ponumber As String​

Dim customername As String​

Dim caseworkername As String​

Dim casetype As String​

Dim dateofsubmission As String​

Dim r As Long​

Dim path As String​

Dim myfilename As String​

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

r = 78​

For r = 78 To lastrow​

If Cells(r, 18).Value = "Invoice Prepared" Then GoTo nextrow​

ponumber = Sheets("IMMIG. TRACKER").Cells(r, 19).Value​

customername = Sheets("IMMIG. TRACKER").Cells(r, 4).Value​

caseworkername = Sheets("IMMIG. TRACKER").Cells(r, 20).Value​

casetype = Sheets("IMMIG. TRACKER").Cells(r, 21).Value​

dateofsubmission = Sheets("IMMIG. TRACKER").Cells(r, 13).Value​

' UnitPrice = Sheets("IMMIG. TRACKER").Cells(r, 20).Value​

' SalesTaxRate = Sheets("IMMIG. TRACKER").Cells(r, 16).Value​

​

Cells(r, 18).Value = "Invoice Prepared"​

Application.DisplayAlerts = False​

Workbooks.Open Filename:= _​

"C:\Users\reuben@relocate.dk\Google Drev\Desktop Shortcuts\PO-IBM-Fragomen Invoice-V2.0.xlsx", UpdateLinks:=xlUpdateLinksAlways​

ActiveWorkbook.Sheets("Invoice").Activate​

ActiveWorkbook.Sheets("Invoice").Range("A13").Value = ponumber​

ActiveWorkbook.Sheets("Invoice").Range("B15").Value = customername​

ActiveWorkbook.Sheets("Invoice").Range("E13").Value = caseworkername​

ActiveWorkbook.Sheets("Invoice").Range("B19").Value = casetype​

ActiveWorkbook.Sheets("Invoice").Range("B26").Value = dateofsubmission​

' ActiveWorkbook.Sheets("Ark1").Range("H21").Value = UnitPrice​

' ActiveWorkbook.Sheets("Ark1").Range("D18").Value = SalesTaxRate​

​

path = "C:\Users\reuben@relocate.dk\Desktop\INVOICES\"​

​

ActiveWorkbook.SaveAs Filename:=path & ponumber & "-" & "IBM" & "-" & customername & ".xlsx"​

myfilename = ActiveWorkbook.FullName​

SetAttr myfilename, vbReadOnly​

Application.DisplayAlerts = True​

'ActiveWorkbook.PrintOut copies:=1​

ActiveWorkbook.Close SaveChanges:=False​

​

nextrow:​

​

Next r​

​

End Sub​

 

[REUBEN]

Hi,

Potentially any string is a name so it really depends on how many non name strings you need to eliminate as to whether the code gets untidy.

If there are only a handful such as None, No or blank it's not so bad:

There are case issues in comparing text see here:
Stop Excel VBA Macro Code Being Case Sensitive & Compare Text. Excel VBA Case Sensitive

I have used OptionCompare at the top of the macro so NONE, None and none are regarded as the same.

You can put code between as shown.

Option Compare Text

Sub Button5_Click()

......


      ActiveWorkbook.Sheets("Invoice").Range("B28").Value = dateofsubmission

 If dependents = "None" Or dependents = "No" Or dependents = "" Then
       ActiveWorkbook.Sheets("Invoice").Range("A20").Value = ""
       ActiveWorkbook.Sheets("Invoice").Range("A25").Value = ""
 Else
      ActiveWorkbook.Sheets("Invoice").Range("A20").Value = " Accompanying " & dependents
      ActiveWorkbook.Sheets("Invoice").Range("A25").Value = " Accompanying " & dependents
 End If

    ActiveWorkbook.Sheets("Invoice").Range("G19").Value = principalfee
 
............

And that just did the trick!

Thank you so much for all your replies, help and patience, Daverunt.

I'm now able to really make the invoices work for me.

Thank you once again.

 

[daverunt]

Glad to help. Thanks for the feedback.