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]

Activity4_8 (3) - Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Adress	City	Province	Postal Code	Country	VAT number	Invoice date	Invoice number	Product code	Description	Rate	Qty	Delivery distance	Product Manager:	Contact Number:
2	Bela Bela Supermarket	12 Church street	Bela Bela	Limpopo	1001	South Africa	1122336811	2023/02/04	ONC100	CABan	Banana	89.13	1	156	Ms Tau	084 321 1221
3	Magalies Tea Garden	3 Baber street	Hartbeespoort	North West	6000	South Africa	1122336899	2023/02/04	ONC101	CATir	Tiramisu	175.65	6	79	Ms Tau	084 321 1221
4	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/04	ONC102	CABan	Banana	89.13	4	1471	Ms Tau	084 321 1221
5	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/04	ONC103	CAPan	Pancakes	78.25	1	1471	Ms Tau	084 321 1221
6	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/06	ONC104	CACrm	Caramel	163.04	8	1420	Ms Tau	084 321 1221
7	Jawsmunger	5 Beach road	Gqeberha	Eastern Cape	2000	South Africa	1122336844	2023/02/06	ONC105	CAFru	Fruit	173.89	8	1068	Ms Tau	084 321 1221
8	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/07	ONC106	CACrm	Caramel	163.04	6	1420	Ms Tau	084 321 1221
9	Waterfall Eatery	34 Mossie avenue	Mbombela	Mpumulanga	5000	South Africa	1122336888	2023/02/07	ONC107	CACrt	Carrot	163.04	9	329	Ms Tau	084 321 1221
10	Guppies	56 Beach road	Durban	Kwazulu Natal	7000	South Africa	1122336910	2023/02/08	ONC108	CACho	Chocolate	163.04	1	573	Ms Tau	084 321 1221
11	Roos Hotel	31 Daisy avenue	Bloemfontein	Free State	4000	South Africa	1122336877	2023/02/09	ONC109	CAPan	Pancakes	78.25	9	421	Ms Tau	084 321 1221
12	Capricorn Inn	51 Unicorn drive	Polokwane	Limpopo	1002	South Africa	1122336822	2023/02/09	ONC110	CAStr	Strawberry	163.04	6	309	Ms Tau	084 321 1221
13	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/10	ONC111	CATir	Tiramisu	175.65	9	1471	Ms Tau	084 321 1221
14	Magalies Tea Garden	3 Baber street	Hartbeespoort	North West	6000	South Africa	1122336899	2023/02/11	ONC112	CABan	Banana	89.13	10	79	Ms Tau	084 321 1221
15	Guppies	56 Beach road	Durban	Kwazulu Natal	7000	South Africa	1122336910	2023/02/13	ONC113	CACho	Chocolate	163.04	3	573	Ms Tau	084 321 1221
16	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/14	ONC114	CARed	Red velvet	184.13	3	1420	Ms Tau	084 321 1221
17	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/14	ONC115	CAOra	Orange	163.04	6	1471	Ms Tau	084 321 1221
18	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/16	ONC116	CAPan	Pancakes	78.25	7	1471	Ms Tau	084 321 1221
19	Guppies	56 Beach road	Durban	Kwazulu Natal	7000	South Africa	1122336910	2023/02/17	ONC117	CAStr	Strawberry	163.04	6	573	Ms Tau	084 321 1221
20	UNISA student café	Preller street	Pretoria	Gauteng	0001	South Africa	1122336833	2023/02/18	ONC118	CABan	Banana	89.13	7	58	Ms Tau	084 321 1221
21	Waterfall Eatery	34 Mossie avenue	Mbombela	Mpumulanga	5000	South Africa	1122336888	2023/02/18	ONC119	CACho	Chocolate	163.04	6	329	Ms Tau	084 321 1221
22	Roos Hotel	31 Daisy avenue	Bloemfontein	Free State	4000	South Africa	1122336877	2023/02/19	ONC120	CAOra	Orange	163.04	7	421	Ms Tau	084 321 1221
23	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/20	ONC121	CATir	Tiramisu	175.65	9	1420	Ms Tau	084 321 1221
24	Magalies Tea Garden	3 Baber street	Hartbeespoort	North West	6000	South Africa	1122336899	2023/02/21	ONC122	CATir	Tiramisu	175.65	9	79	Ms Tau	084 321 1221
25	UNISA student café	Preller street	Pretoria	Gauteng	0001	South Africa	1122336833	2023/02/21	ONC123	CACrm	Caramel	163.04	2	58	Ms Tau	084 321 1221
26	Lulu's Bakery	321 Justice street	Pretoria	Gauteng	0001	South Africa	1122336789	2023/02/22	ONC124	CACho	Chocolate	163.04	6	58	Ms Tau	084 321 1221
27	Jawsmunger	5 Beach road	Gqeberha	Eastern Cape	2000	South Africa	1122336844	2023/02/25	ONC125	CACrt	Carrot	163.04	4	1068	Ms Tau	084 321 1221
28	Capricorn Inn	51 Unicorn drive	Polokwane	Limpopo	1002	South Africa	1122336822	2023/02/26	ONC126	CACho	Chocolate	163.04	8	309	Ms Tau	084 321 1221
29	UNISA student café	Preller street	Pretoria	Gauteng	0001	South Africa	1122336833	2023/02/27	ONC127	CACrt	Carrot	163.04	5	58	Ms Tau	084 321 1221
30	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/27	ONC128	CARed	Red velvet	184.13	6	1420	Ms Tau	084 321 1221
31	Lulu's Bakery	321 Justice street	Pretoria	Gauteng	0001	South Africa	1122336789	2023/02/28	ONC129	CAStr	Strawberry	163.04	1	58	Ms Tau	084 321 1221
32	Sophia's Tea Garden	45 Tau avenue	Johannesburg	Gauteng	0002	South Africa	1122336800	2023/02/28	ONC130	CALem	Lemon Meringue	173.89	7	19	Ms Tau	084 321 1221
Recorded_Sales

 

[PeteWright]

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.

 

[joyrichter]

Activity4_8 (3) - Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Adress	City	Province	Postal Code	Country	VAT number	Invoice date	Invoice number	Product code	Description	Rate	Qty	Delivery distance	Product Manager:	Contact Number:
2	Bela Bela Supermarket	12 Church street	Bela Bela	Limpopo	1001	South Africa	1122336811	2023/02/04	ONC100	CABan	Banana	89.13	1	156	Ms Tau	084 321 1221
3	Magalies Tea Garden	3 Baber street	Hartbeespoort	North West	6000	South Africa	1122336899	2023/02/04	ONC101	CATir	Tiramisu	175.65	6	79	Ms Tau	084 321 1221
4	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/04	ONC102	CABan	Banana	89.13	4	1471	Ms Tau	084 321 1221
5	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/04	ONC103	CAPan	Pancakes	78.25	1	1471	Ms Tau	084 321 1221
6	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/06	ONC104	CACrm	Caramel	163.04	8	1420	Ms Tau	084 321 1221
7	Jawsmunger	5 Beach road	Gqeberha	Eastern Cape	2000	South Africa	1122336844	2023/02/06	ONC105	CAFru	Fruit	173.89	8	1068	Ms Tau	084 321 1221
8	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/07	ONC106	CACrm	Caramel	163.04	6	1420	Ms Tau	084 321 1221
9	Waterfall Eatery	34 Mossie avenue	Mbombela	Mpumulanga	5000	South Africa	1122336888	2023/02/07	ONC107	CACrt	Carrot	163.04	9	329	Ms Tau	084 321 1221
10	Guppies	56 Beach road	Durban	Kwazulu Natal	7000	South Africa	1122336910	2023/02/08	ONC108	CACho	Chocolate	163.04	1	573	Ms Tau	084 321 1221
11	Roos Hotel	31 Daisy avenue	Bloemfontein	Free State	4000	South Africa	1122336877	2023/02/09	ONC109	CAPan	Pancakes	78.25	9	421	Ms Tau	084 321 1221
12	Capricorn Inn	51 Unicorn drive	Polokwane	Limpopo	1002	South Africa	1122336822	2023/02/09	ONC110	CAStr	Strawberry	163.04	6	309	Ms Tau	084 321 1221
13	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/10	ONC111	CATir	Tiramisu	175.65	9	1471	Ms Tau	084 321 1221
14	Magalies Tea Garden	3 Baber street	Hartbeespoort	North West	6000	South Africa	1122336899	2023/02/11	ONC112	CABan	Banana	89.13	10	79	Ms Tau	084 321 1221
15	Guppies	56 Beach road	Durban	Kwazulu Natal	7000	South Africa	1122336910	2023/02/13	ONC113	CACho	Chocolate	163.04	3	573	Ms Tau	084 321 1221
16	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/14	ONC114	CARed	Red velvet	184.13	3	1420	Ms Tau	084 321 1221
17	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/14	ONC115	CAOra	Orange	163.04	6	1471	Ms Tau	084 321 1221
18	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/16	ONC116	CAPan	Pancakes	78.25	7	1471	Ms Tau	084 321 1221
19	Guppies	56 Beach road	Durban	Kwazulu Natal	7000	South Africa	1122336910	2023/02/17	ONC117	CAStr	Strawberry	163.04	6	573	Ms Tau	084 321 1221
20	UNISA student café	Preller street	Pretoria	Gauteng	0001	South Africa	1122336833	2023/02/18	ONC118	CABan	Banana	89.13	7	58	Ms Tau	084 321 1221
21	Waterfall Eatery	34 Mossie avenue	Mbombela	Mpumulanga	5000	South Africa	1122336888	2023/02/18	ONC119	CACho	Chocolate	163.04	6	329	Ms Tau	084 321 1221
22	Roos Hotel	31 Daisy avenue	Bloemfontein	Free State	4000	South Africa	1122336877	2023/02/19	ONC120	CAOra	Orange	163.04	7	421	Ms Tau	084 321 1221
23	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/20	ONC121	CATir	Tiramisu	175.65	9	1420	Ms Tau	084 321 1221
24	Magalies Tea Garden	3 Baber street	Hartbeespoort	North West	6000	South Africa	1122336899	2023/02/21	ONC122	CATir	Tiramisu	175.65	9	79	Ms Tau	084 321 1221
25	UNISA student café	Preller street	Pretoria	Gauteng	0001	South Africa	1122336833	2023/02/21	ONC123	CACrm	Caramel	163.04	2	58	Ms Tau	084 321 1221
26	Lulu's Bakery	321 Justice street	Pretoria	Gauteng	0001	South Africa	1122336789	2023/02/22	ONC124	CACho	Chocolate	163.04	6	58	Ms Tau	084 321 1221
27	Jawsmunger	5 Beach road	Gqeberha	Eastern Cape	2000	South Africa	1122336844	2023/02/25	ONC125	CACrt	Carrot	163.04	4	1068	Ms Tau	084 321 1221
28	Capricorn Inn	51 Unicorn drive	Polokwane	Limpopo	1002	South Africa	1122336822	2023/02/26	ONC126	CACho	Chocolate	163.04	8	309	Ms Tau	084 321 1221
29	UNISA student café	Preller street	Pretoria	Gauteng	0001	South Africa	1122336833	2023/02/27	ONC127	CACrt	Carrot	163.04	5	58	Ms Tau	084 321 1221
30	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/27	ONC128	CARed	Red velvet	184.13	6	1420	Ms Tau	084 321 1221
31	Lulu's Bakery	321 Justice street	Pretoria	Gauteng	0001	South Africa	1122336789	2023/02/28	ONC129	CAStr	Strawberry	163.04	1	58	Ms Tau	084 321 1221
32	Sophia's Tea Garden	45 Tau avenue	Johannesburg	Gauteng	0002	South Africa	1122336800	2023/02/28	ONC130	CALem	Lemon Meringue	173.89	7	19	Ms Tau	084 321 1221
Recorded_Sales

 

[joyrichter]

Hi
sorry i"m new at this....

I need to generate invoice from the data sheet above

 

[joyrichter]

Activity4_8 (3) - Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Adress	City	Province	Postal Code	Country	VAT number	Invoice date	Invoice number	Product code	Description	Rate	Qty	Delivery distance	Product Manager:	Contact Number:
2	Bela Bela Supermarket	12 Church street	Bela Bela	Limpopo	1001	South Africa	1122336811	2023/02/04	ONC100	CABan	Banana	89.13	1	156	Ms Tau	084 321 1221
3	Magalies Tea Garden	3 Baber street	Hartbeespoort	North West	6000	South Africa	1122336899	2023/02/04	ONC101	CATir	Tiramisu	175.65	6	79	Ms Tau	084 321 1221
4	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/04	ONC102	CABan	Banana	89.13	4	1471	Ms Tau	084 321 1221
5	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/04	ONC103	CAPan	Pancakes	78.25	1	1471	Ms Tau	084 321 1221
6	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/06	ONC104	CACrm	Caramel	163.04	8	1420	Ms Tau	084 321 1221
7	Jawsmunger	5 Beach road	Gqeberha	Eastern Cape	2000	South Africa	1122336844	2023/02/06	ONC105	CAFru	Fruit	173.89	8	1068	Ms Tau	084 321 1221
8	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/07	ONC106	CACrm	Caramel	163.04	6	1420	Ms Tau	084 321 1221
9	Waterfall Eatery	34 Mossie avenue	Mbombela	Mpumulanga	5000	South Africa	1122336888	2023/02/07	ONC107	CACrt	Carrot	163.04	9	329	Ms Tau	084 321 1221
10	Guppies	56 Beach road	Durban	Kwazulu Natal	7000	South Africa	1122336910	2023/02/08	ONC108	CACho	Chocolate	163.04	1	573	Ms Tau	084 321 1221
11	Roos Hotel	31 Daisy avenue	Bloemfontein	Free State	4000	South Africa	1122336877	2023/02/09	ONC109	CAPan	Pancakes	78.25	9	421	Ms Tau	084 321 1221
12	Capricorn Inn	51 Unicorn drive	Polokwane	Limpopo	1002	South Africa	1122336822	2023/02/09	ONC110	CAStr	Strawberry	163.04	6	309	Ms Tau	084 321 1221
13	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/10	ONC111	CATir	Tiramisu	175.65	9	1471	Ms Tau	084 321 1221
14	Magalies Tea Garden	3 Baber street	Hartbeespoort	North West	6000	South Africa	1122336899	2023/02/11	ONC112	CABan	Banana	89.13	10	79	Ms Tau	084 321 1221
15	Guppies	56 Beach road	Durban	Kwazulu Natal	7000	South Africa	1122336910	2023/02/13	ONC113	CACho	Chocolate	163.04	3	573	Ms Tau	084 321 1221
16	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/14	ONC114	CARed	Red velvet	184.13	3	1420	Ms Tau	084 321 1221
17	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/14	ONC115	CAOra	Orange	163.04	6	1471	Ms Tau	084 321 1221
18	Klapoog's Castle	89 Seahorse avenue	Langebaan	Western Cape	3001	South Africa	1122336866	2023/02/16	ONC116	CAPan	Pancakes	78.25	7	1471	Ms Tau	084 321 1221
19	Guppies	56 Beach road	Durban	Kwazulu Natal	7000	South Africa	1122336910	2023/02/17	ONC117	CAStr	Strawberry	163.04	6	573	Ms Tau	084 321 1221
20	UNISA student café	Preller street	Pretoria	Gauteng	0001	South Africa	1122336833	2023/02/18	ONC118	CABan	Banana	89.13	7	58	Ms Tau	084 321 1221
21	Waterfall Eatery	34 Mossie avenue	Mbombela	Mpumulanga	5000	South Africa	1122336888	2023/02/18	ONC119	CACho	Chocolate	163.04	6	329	Ms Tau	084 321 1221
22	Roos Hotel	31 Daisy avenue	Bloemfontein	Free State	4000	South Africa	1122336877	2023/02/19	ONC120	CAOra	Orange	163.04	7	421	Ms Tau	084 321 1221
23	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/20	ONC121	CATir	Tiramisu	175.65	9	1420	Ms Tau	084 321 1221
24	Magalies Tea Garden	3 Baber street	Hartbeespoort	North West	6000	South Africa	1122336899	2023/02/21	ONC122	CATir	Tiramisu	175.65	9	79	Ms Tau	084 321 1221
25	UNISA student café	Preller street	Pretoria	Gauteng	0001	South Africa	1122336833	2023/02/21	ONC123	CACrm	Caramel	163.04	2	58	Ms Tau	084 321 1221
26	Lulu's Bakery	321 Justice street	Pretoria	Gauteng	0001	South Africa	1122336789	2023/02/22	ONC124	CACho	Chocolate	163.04	6	58	Ms Tau	084 321 1221
27	Jawsmunger	5 Beach road	Gqeberha	Eastern Cape	2000	South Africa	1122336844	2023/02/25	ONC125	CACrt	Carrot	163.04	4	1068	Ms Tau	084 321 1221
28	Capricorn Inn	51 Unicorn drive	Polokwane	Limpopo	1002	South Africa	1122336822	2023/02/26	ONC126	CACho	Chocolate	163.04	8	309	Ms Tau	084 321 1221
29	UNISA student café	Preller street	Pretoria	Gauteng	0001	South Africa	1122336833	2023/02/27	ONC127	CACrt	Carrot	163.04	5	58	Ms Tau	084 321 1221
30	Mermaid Bakery	123 Strand street	Cape Town	Western Cape	3000	South Africa	1122336855	2023/02/27	ONC128	CARed	Red velvet	184.13	6	1420	Ms Tau	084 321 1221
31	Lulu's Bakery	321 Justice street	Pretoria	Gauteng	0001	South Africa	1122336789	2023/02/28	ONC129	CAStr	Strawberry	163.04	1	58	Ms Tau	084 321 1221
32	Sophia's Tea Garden	45 Tau avenue	Johannesburg	Gauteng	0002	South Africa	1122336800	2023/02/28	ONC130	CALem	Lemon Meringue	173.89	7	19	Ms Tau	084 321 1221
Recorded_Sales

 

[joyrichter]

Activity4_8 (3) - Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	1
2	2				Ikhishi Likagogo (Pty) Ltd				Isando
3	3				086 121 4311				Gauteng
4	4								0034
5	5								South Africa
6									VAT number: 99123456781
7
8	Customer Name:		Invoice date:		Tax invoice number:					Amount due (ZAR)
9										-
10
11			Due dute:
12
13
14
15
16
17
18	Code	Description							Rate	Qty		Total
19												-
20												-
21									Subtotal			-
22									VAT (15%)			-
23									Total			-
24
25	For any queries please contact the product manager. Details are provided below:
26	Product Manager:
27	Contact Number:
28
29	Delivery:
30	Delivery is charged at R2.07 (VAT inclusive) for delivery in South Africa.
31
Invoice_Template

Cell Formulas
Range	Formula
J9	J9	=L23
L19:L20	L19	=I19*J19
L21,L23	L21	=L19+L20
L22	L22	=L21*0.15

 

[PeteWright]

okay, and how should the invoice look like?
You could create an sample Excel sheet that shows the invoice.

 

[joyrichter]

I Have sent a invoice template with xl2bb
Is this sufficient.

Here is the code I Have written:


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











End Sub








































'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

 

[joyrichter]

okay, and how should the invoice look like?
You could create an sample Excel sheet that shows the invoice.

How do I sent the complete document I working on?