VBA to generate invoices from data base

[joyrichter]

Hi
Im getting stuck on a code, I have gotten the code to generate an invoice from a database worksheet, I want to copy the code for the complete sheet, to generate different invoices and to rename the generated invoices.
I will appreciate any tips and help.
Thank you
Joy

 

[joyrichter]

Well, you provided as good as no information, so it's almost impossible to help you.
Please provide more information like the code, some sample data, a.s.o.

__________
EDIT: ok, there's at least some data to start with. Now please give some more info on what you would like to achieve.

Hi

I want to use the information in the database to generate invoice using the invoice template, the invoices should be generated on seperate worksheets and the worksheets have to renamed to the invoice number

Thanks I appreciate the help

 

[PeteWright]

How do I sent the complete document I working on?

I'm afraid, you can't. I also tried to upload my Excel file, but I think it's not possible. Maybe someone else knows how.

 

[joyrichter]

Maybe you can review my code and suggest where I going wrong

 

[PeteWright]

Hi

I want to use the information in the database to generate invoice using the invoice template, the invoices should be generated on seperate worksheets and the worksheets have to renamed to the invoice number

Thanks I appreciate the help

OK, I'll take a look at it.

 

[joyrichter]

Thank you i really appreciate it, ive been sitting with it for 3 days...

 

[joyrichter]

Sub To_Generate_Invoices()

'Coded by: J Richter
'Student Number: 35477865
'Date: 15 June 2023
'Module: AIN 3701
 'Activity 4.8

'Variable declaration:
Dim RecordedSalesWS As Worksheet
Dim InvoiceTemplateWS 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 a As Long
Dim rng_dest As Range
Dim rng As Range
Dim i As Long
Dim Counter As String
Dim Val As Integer

'Copy_data_to_Invoice_Template
 
  Application.ScreenUpdating = False
  i = 1
  Set rng_dest = Sheets("Recorded_Sales").Range("A:P")
   ' Find first empty row in columns A:P on sheet Recorded_Sales
 Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0
    i = i + 1
  Loop
   'Copy range A2:P32 on sheet Invoice_Template to Variant array
  Set rng = Sheets("Invoice_Template").Range("A2:P32")
   ' Copy rows containing values to sheet Invoice_Template
 
 For a = 1 To rng.Rows.Count
    If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then
      rng_dest.Rows(i).Value = rng.Rows(a).Value
       'Copy Invoice number
      Sheets("Recorded_Sales").Range("I" & i).Value = Sheets("Invoice_Template").Range("E9").Value
       'Copy Date
      Sheets("Recorded_Sales").Range("H" & i).Value = Sheets("Invoice_Template").Range("C9").Value
       'Copy Costumer name
      Sheets("Recorded_Sales").Range("A" & i).Value = Sheets("Invoice_Template").Range("A9").Value
       'Copy Costumer Adress
      Sheets("Recorded_Sales").Range("B" & i).Value = Sheets("Invoice_Template").Range("A10").Value
       'Copy Costumer City
      Sheets("Recorded_Sales").Range("C" & i).Value = Sheets("Invoice_Template").Range("A11").Value
       'Copy Costumer Province
      Sheets("Recorded_Sales").Range("D" & i).Value = Sheets("Invoice_Template").Range("A12").Value
       'Copy Costumer Postal Code
      Sheets("Recorded_Sales").Range("E" & i).Value = Sheets("Invoice_Template").Range("A13").Value
       'Copy Costumer Country
      Sheets("Recorded_Sales").Range("F" & i).Value = Sheets("Invoice_Template").Range("A14").Value
       'Copy Costumer VAT Number
      Sheets("Recorded_Sales").Range("G" & i).Value = Sheets("Invoice_Template").Range("A15").Value
       'Copy Delivery Distance
      Sheets("Recorded_Sales").Range("N" & i).Value = Sheets("Invoice_Template").Range("J20").Value
       'Copy Product Code
      Sheets("Recorded_Sales").Range("J" & i).Value = Sheets("Invoice_Template").Range("A19").Value
       'Copy Product Description
      Sheets("Recorded_Sales").Range("K" & i).Value = Sheets("Invoice_Template").Range("B19").Value
       'Copy Product Manager
      Sheets("Recorded_Sales").Range("O" & i).Value = Sheets("Invoice_Template").Range("C26").Value
       'Copy Product Manager Contact Number
      Sheets("Recorded_Sales").Range("P" & i).Value = Sheets("Invoice_Template").Range("C27").Value
       'Copy Product Rate
      Sheets("Recorded_Sales").Range("L" & i).Value = Sheets("Invoice_Template").Range("I19").Value
       'Copy Quantity
      Sheets("Recorded_Sales").Range("M" & i).Value = Sheets("Invoice_Template").Range("J19").Value
       'Copy Delivery Distance
      Sheets("Recorded_Sales").Range("N" & i).Value = Sheets("Invoice_Template").Range("B20").Value
       'Copy Delivery Distance
      Sheets("Recorded_Sales").Range("N" & i).Value = Sheets("Invoice_Template").Range("B20").Value
                                                       
      i = i + 1
    End If
  Next a
  Application.ScreenUpdating = True
  'Constant declaration
Const delRate = 1.8

'Set workbook variables to worksheets
Set RecordedSalesWS = Worksheets("Recorded_Sales")
Set InvoiceTemplateWS = Worksheets("Invoice_Template")



'Assign values to variables
Worksheets("Recorded_Sales").Select
customerName = Range("A2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("A9").Value = customerName

Worksheets("Recorded_Sales").Select
customerAddress = RecordedSalesWS.Range("B2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("A10").Value = customerAddress

Worksheets("Recorded_Sales").Select
customerCity = RecordedSalesWS.Range("C2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("A11").Value = customerCity


Worksheets("Recorded_Sales").Select
customerProvince = RecordedSalesWS.Range("D2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("A12").Value = customerProvince

Worksheets("Recorded_Sales").Select
customerPostalCode = RecordedSalesWS.Range("E2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("A13").Value = customerPostalCode

Worksheets("Recorded_Sales").Select
customerCountry = RecordedSalesWS.Range("F2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("A14").Value = customerCountry

Worksheets("Recorded_Sales").Select
customerVATnumber = RecordedSalesWS.Range("G2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("A15").Value = "VAT Number:" & customerVATnumber

Worksheets("Recorded_Sales").Select
DeliveryDistance = RecordedSalesWS.Range("N2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("J20").Value = DeliveryDistance

Worksheets("Recorded_Sales").Select
invoiceDate = Range("H2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("C9").Value = invoiceDate

Worksheets("Recorded_Sales").Select
TaxinvoiceNumber = Range("I2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("E9").Value = TaxinvoiceNumber

Worksheets("Recorded_Sales").Select
productCode = Range("J2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("A19").Value = productCode

Worksheets("Recorded_Sales").Select
productDescription = Range("K2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("B19").Value = productDescription

Worksheets("Recorded_Sales").Select
productionManager = Range("O2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("C26").Value = productionManager

Worksheets("Recorded_Sales").Select
contactNumber = Range("P2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("C27").Value = contactNumber

Worksheets("Recorded_Sales").Select
productRate = Range("L2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("I19").Value = productRate

Worksheets("Recorded_Sales").Select
productQuantity = Range("M2")
Worksheets("Invoice_Template").Select
Worksheets("Invoice_Template").Range("J19").Value = productQuantity


Worksheets("Recorded_Sales").Select
productQuantity = RecordedSalesWS.Range("M2")
Worksheets("Invoice_Template").Select
InvoiceTemplateWS.Range("J19") = productQuantity

Worksheets("Recorded_Sales").Select
DeliveryDistance = RecordedSalesWS.Range("N2")
Worksheets("Invoice_Template").Select
InvoiceTemplateWS.Range("B20") = "Kilometers - Isando to " & customerCity

Worksheets("Recorded_Sales").Select
productRate = RecordedSalesWS.Range("L2")
Worksheets("Invoice_Template").Select
InvoiceTemplateWS.Range("I20") = delRate

'Constant declaration
Const deliveryRate = 1.8


'Calculations
InvoiceTemplateWS.Range("C12") = invoiceDate + 7

'Fixed code
InvoiceTemplateWS.Range("A20") = "Delivery"


'formatting in destination sheet
InvoiceTemplateWS.Range("A13").NumberFormat = "0000"

'Activate invoice sheet
InvoiceTemplateWS.Activate


 
 'Event code that runs if a cell value is changed
Private Sub Worksheet_Change(ByVal Target As Range)
                                                       
'Check if the cell value is in column I
If Not Intersect(Target, Range("B:B")) Is Nothing Then
                                                       
     'Copy worksheet based on value in cell E2 in worksheet Sheet1 and put it last
    Sheets(Worksheets("Sheet1").Range("E2").Value).Copy , Sheets(Sheets.Count)
                                                       
     'Rename worksheet to the value you entered.
    ActiveSheet.Name = Target.Value
                                                       
End If
                                                       
'Go back to worksheet Sheet1
Worksheets("Sheet1").Activate
 
 
 
 
  End Sub

 

[PeteWright]

Hi @joyrichter

I have a few questions about your code:

1. How did you create the code - Did you record a Macro or have you typed the code all by yourself?
2. Your FOR-LOOP does not create any new worksheets, instead it overwrites the data with the data from the next row
3. In your FOR-LOOP there may be an error:
   

   Sheets("Recorded_Sales").Range("I" & i).Value = Sheets("Invoice_Template").Range("E9").Value

   This way you assign data from the invoice sheet to the data sheet.
   I think you meant it the other way round (swap the code on both sides of the equal sign and you get it
   

   Sheets("Invoice_Template").Range("E9").Value = Sheets("Recorded_Sales").Range("I" & i).Value

Please answer those questions first and I will then take a further look at the code and adjust it a little bit.

 

[joyrichter]

Hi @joyrichter

I have a few questions about your code:

1. How did you create the code - Did you record a Macro or have you typed the code all by yourself?
2. Your FOR-LOOP does not create any new worksheets, instead it overwrites the data with the data from the next row
3. In your FOR-LOOP there may be an error:
   

   Sheets("Recorded_Sales").Range("I" & i).Value = Sheets("Invoice_Template").Range("E9").Value

   This way you assign data from the invoice sheet to the data sheet.
   I think you meant it the other way round (swap the code on both sides of the equal sign and you get it
   

   Sheets("Invoice_Template").Range("E9").Value = Sheets("Recorded_Sales").Range("I" & i).Value

Please answer those questions first and I will then take a further look at the code and adjust it a little bit.

Good Morning PeteWright
@PeteWright
1.I wrote the code by myself....
2. how do i fix the FOR-LOOP code, I want to create new worksheets?
3. Yes that is an error, I swaped the codes around...

Thank you, will you be able to help me with the FOR -LOOP, I think that is my biggest problem....

 

[PeteWright]

Alright @joyrichter I'm on it. Just two more questions:

1. As I can see, some Names appear multiple times. Should there be an invoice for each Name or for each row?
   If it's one invoice per each row, we're lucky, otherwise it requires a lot more coding.
2. Is the layout (the design) of the invoice "fixed" or can it look different?
   If it is not "fixed", I suggest a separate (recorded) macro that creates a new sheet with the invoice layout that can be used multiple times (for each invoice).

Can you please answer these questions.

 

[joyrichter]

Alright @joyrichter I'm on it. Just two more questions:

1. As I can see, some Names appear multiple times. Should there be an invoice for each Name or for each row?
   If it's one invoice per each row, we're lucky, otherwise it requires a lot more coding.
2. Is the layout (the design) of the invoice "fixed" or can it look different?
   If it is not "fixed", I suggest a separate (recorded) macro that creates a new sheet with the invoice layout that can be used multiple times (for each invoice).

Can you please answer these questions.

Hi Pete,
1. There should be 1 Invoice for each row , Linked to the invoice number (Column I), The invoice should be named as the invoice number
2. The design of the invoice is a template, so I need to use the format for each invoice.

Vielen Dank...