'Invoice Generator
Sub To_Generate_Invoices()
'Constant declaration
Const deliveryRate = 1.8
'Variable declaration:
Dim RecordedSalesWS As Worksheet
Dim InvoiceTemplateWS As Worksheet
Dim currentWorkingWS As Worksheet
Dim customerName As String
Dim customerAddress As String
Dim customerCity As String
Dim customerProvince As String
Dim customerPostalCode As Single
Dim customerCountry As String
Dim customerVATnumber As String
Dim invoiceDate As Date
Dim TaxinvoiceNumber As String
Dim productCode As String
Dim productDescription As String
Dim productRate As Single
Dim productQuantity As Integer
Dim deliveryDistance As Integer
Dim productionManager As String
Dim contactNumber As String
Dim rng_dest As Range
Dim rng As Range
Dim i As Long
Dim numberOfDatasets As Long
Dim numberOfInconsistentRows As Long
Dim dataInconsistency As Boolean
'Set workbook variables to worksheets
Set RecordedSalesWS = ThisWorkbook.Sheets("Recorded_Sales")
Set InvoiceTemplateWS = ThisWorkbook.Sheets("Invoice_Template")
Set rng_dest = ThisWorkbook.Sheets("Recorded_Sales").Range("A:P")
numberOfDatasets = 0
numberOfInconsistentRows = 0
dataInconsistency = False
'search for last row with data and check consistency (blank cells)
i = 2
numberOfInconsistentRows = 0
Do While rng_dest.Cells(i, 1) <> ""
'loop through columns A to P (column 1 - 16), look for and count blank cells
For j = 1 To 16
If rng_dest.Cells(i, j).Value = "" Then numberOfInconsistentRows = numberOfInconsistentRows + 1
Next j
If numberOfInconsistentRows > 0 Then
dataInconsistency = True
End If
numberOfInconsistentRows = 0
i = i + 1
numberOfDatasets = numberOfDatasets + 1
Loop
If numberOfDatasets = 0 Then
MsgBox "No data detected in first row. Macro will stop."
Exit Sub
End If
If dataInconsistency = True Then
MsgBox "Inconsistent data. Please check for blank cells. Macro will stop"
Exit Sub
End If
MsgBox numberOfDatasets & " Datasets found." & vbCrLf & "Generating invoices."
Application.ScreenUpdating = False
'write data to invoice template
For i = 1 To numberOfDatasets
customerName = RecordedSalesWS.Range("A" & i + 1).Value
customerAddress = RecordedSalesWS.Range("B" & i + 1).Value
customerCity = RecordedSalesWS.Range("C" & i + 1).Value
customerProvince = RecordedSalesWS.Range("D" & i + 1).Value
customerPostalCode = RecordedSalesWS.Range("E" & i + 1).Value
customerCountry = RecordedSalesWS.Range("F" & i + 1).Value
customerVATnumber = RecordedSalesWS.Range("G" & i + 1).Value
invoiceDate = RecordedSalesWS.Range("H" & i + 1).Value
TaxinvoiceNumber = RecordedSalesWS.Range("I" & i + 1).Value
productCode = RecordedSalesWS.Range("J" & i + 1).Value
productDescription = RecordedSalesWS.Range("K" & i + 1).Value
productRate = RecordedSalesWS.Range("L" & i + 1).Value
productQuantity = RecordedSalesWS.Range("M" & i + 1).Value
deliveryDistance = RecordedSalesWS.Range("N" & i + 1).Value
productionManager = RecordedSalesWS.Range("O" & i + 1).Value
contactNumber = RecordedSalesWS.Range("P" & i + 1).Value
InvoiceTemplateWS.Copy After:=Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = TaxinvoiceNumber
On Error GoTo 0
Set currentWorkingWS = ThisWorkbook.Sheets(TaxinvoiceNumber)
With currentWorkingWS
.Range("A9").Value = customerName
.Range("A10").Value = customerAddress
.Range("A11").Value = customerCity
.Range("A12").Value = customerProvince
.Range("A13").Value = customerPostalCode
.Range("A13").NumberFormat = "0000"
.Range("A14").Value = customerCountry
.Range("A15").Value = "VAT Number:" & customerVATnumber
.Range("C9").Value = invoiceDate
.Range("C12") = invoiceDate + 7
.Range("E9").Value = TaxinvoiceNumber
.Range("A19").Value = productCode
.Range("B19").Value = productDescription
.Range("I19").Value = productRate
.Range("J19").Value = productQuantity
.Range("I20").Value = deliveryRate
.Range("J20").Value = deliveryDistance
.Range("A20").Value = "Delivery:"
.Range("B20").Value = deliveryDistance
.Range("C20").Value = "km - Isando to " & customerCity
.Range("C26").Value = productionManager
.Range("C27").Value = contactNumber
End With
Next i
Application.ScreenUpdating = True
End Sub
