Trying to Auto Clear a new template invoice, when I already have an auto clearing invoice on another sheet

[Kym_beginner_in_VBA]

Hi

I have been trying for the last 2 days to auto-clear a complex new invoice that I have created this week. My workbook already contains a working basic invoice for clients which is linked to an address table on another sheet. The 1st Invoice has 2 Macro buttons which was done last year, the 1st to clear the invoice and increment the invoice number, the 2nd to save the invoice as a PDF version. so that works all great. what I've had to do this year is create a shipping commercial invoice which I have now linked via dropdown and code to auto populate the client details from the same table i created last year, so that part works fine. I've also created a new items table which also auto sorts as I add more and more items to that table (in time I expect that table of items list to be many hundreds of items long!) The invoice I created has a max allowed number of 40 items which I can add to the invoice and all the automation on weights, prices etc. all work including the dynamic filter of only the items selected (non blank rows) go onto the invoice and any blanks up to a total of 40 items simply do not appear on the printable and viewed page until I select a new item from my table select item.
Now I've tried copying and writing the auto clear code a few times and I've searched online for how to make it work but I can only find the explanation for one invoice in a worksheet. I have tried opening a second module and trying my best with various tries, but I'm getting nowhere. I do have xl2bb downloaded too but there isn't an option for me in this post to upload the mini sheet.

 

[Kym_beginner_in_VBA]

I

Hi

I have been trying for the last 2 days to auto-clear a complex new invoice that I have created this week. My workbook already contains a working basic invoice for clients which is linked to an address table on another sheet. The 1st Invoice has 2 Macro buttons which was done last year, the 1st to clear the invoice and increment the invoice number, the 2nd to save the invoice as a PDF version. so that works all great. what I've had to do this year is create a shipping commercial invoice which I have now linked via dropdown and code to auto populate the client details from the same table i created last year, so that part works fine. I've also created a new items table which also auto sorts as I add more and more items to that table (in time I expect that table of items list to be many hundreds of items long!) The invoice I created has a max allowed number of 40 items which I can add to the invoice and all the automation on weights, prices etc. all work including the dynamic filter of only the items selected (non-blank rows) go onto the invoice and any blanks up to a total of 40 items simply do not appear on the printable and viewed page until I select a new item from my table select item.
Now I've tried copying and writing the auto clear code a few times and I've searched online for how to make it work but I can only find the explanation for one invoice in a worksheet. I have tried opening a second module and trying my best with various tries, but I'm getting nowhere. I do have xl2bb downloaded too but there isn't an option for me in this post to upload the mini sheet.

I should add that I need to create 2 more Macro buttons after this is fixed. one to save the invoice as a PDF and one to export most but not all of the data to create a separate copy PDF invoice that will look different. hopefully that won't be too hard, but one step at a time.

 

[Kym_beginner_in_VBA]

Hi

I have been trying for the last 2 days to auto-clear a complex new invoice that I have created this week. My workbook already contains a working basic invoice for clients which is linked to an address table on another sheet. The 1st Invoice has 2 Macro buttons which was done last year, the 1st to clear the invoice and increment the invoice number, the 2nd to save the invoice as a PDF version. so that works all great. what I've had to do this year is create a shipping commercial invoice which I have now linked via dropdown and code to auto populate the client details from the same table i created last year, so that part works fine. I've also created a new items table which also auto sorts as I add more and more items to that table (in time I expect that table of items list to be many hundreds of items long!) The invoice I created has a max allowed number of 40 items which I can add to the invoice and all the automation on weights, prices etc. all work including the dynamic filter of only the items selected (non blank rows) go onto the invoice and any blanks up to a total of 40 items simply do not appear on the printable and viewed page until I select a new item from my table select item.
Now I've tried copying and writing the auto clear code a few times and I've searched online for how to make it work but I can only find the explanation for one invoice in a worksheet. I have tried opening a second module and trying my best with various tries, but I'm getting nowhere. I do have xl2bb downloaded too but there isn't an option for me in this post to upload the mini sheet.

Invoicing Database.xlsm
	A	B	C	D	E	F	G	H	I		K
1		INVOICE
2
3	DATE:
4
5	SHIPPER / SENDER						CONSIGNEE / RECEIVER
6	NAME			NAME	Alan Imports LTD
7	ADDRESS			ADDRESS	LU 407, 1 Filament walk
8					Wandsworth
9					London
10	POST CODE			POST CODE	SW18 4GQ
11	TEL:			TEL:
12	COUNTRY			COUNTRY
13
14
15	AIRWAY TRACKING							CURRENCY	GBP
16
17			COUNTRY OF ORIGIN	HARM. CODE	# OF UNITS	COST PER UNIT	ITEM WEIGHT	TOTAL VALUE
18		ITEM, DESCRIPTION & INGREDIENTS
19								Weight	Amount
21		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
22		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
23		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
24		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
25		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
26		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
27		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
28		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
29		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
30		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
31		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
32		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
33		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
34		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
35		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
36		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
37		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
38		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
39		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
40		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
41		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
42		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
43		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
44		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
45		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
46		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
47		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
48		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
49		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
50		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
51		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
52		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
53		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
54		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
55		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
56		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
57		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
58		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
59		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
60		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
61		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
62		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
63		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
64		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
65		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
66		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
67		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
68		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
69		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
70		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
71		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
72		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
73		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
74		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
75		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
76		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
77		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
78		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
79		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
80		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
81		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
82		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
83		RICE NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
84		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
85		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
86		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
87		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
88		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
89		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
90		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
91		RICE NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
92		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
93		VERMICELLI NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
94		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
95		RICE NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
96		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
97		RICE NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
98		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
99		GLASS NOODLES (with sauce & seasoning)	Thailand	1902.3010.30	6	£1.00	50 gr	300 gr	£6.00
100		Rice flour, concentrated sauce sachet, seasoning sachet, dried chilli powder, dried garlic flakes, (DOES NOT CONTAIN MEAT)
101
102
103	EORI: GB082654205000		Total number of items		240		INVOICE TOTAL		£240.00
104	SHIPPING TERMS: DDP
105	REASON FOR EXPORT: PERSONAL HOME DOMESTIC USE ONLY
106							Net Total Weight		12.00 Kg
107	Box 1 Size:
108	Box 2 Size:
109	Box 3 Size:
110
111		DECLARATION STATEMENT			SHIPPER SIGNATURE
112		I hereby certify that the information in this invoice is true and correct as presented to us by the receiver and that the content descriptions and values of this shipment are as stated above and are for home use and personal consumption only and not for resale.
113
114
115
CREATE INVOICES

Cell Formulas
Range	Formula
E7	E7	=IFNA(VLOOKUP($E$6,Cust_list,2,0),"")
E8	E8	=IFNA(VLOOKUP($E$6,Cust_list,3,0),"")
E9	E9	=IFNA(VLOOKUP($E$6,Cust_list,4,0),"")
E10	E10	=IFNA(VLOOKUP($E$6,Cust_list,5,0),"")
H21,H23,H25,H27,H29,H31,H33,H35,H37,H39,H41,H43,H45,H47,H49,H51,H53,H55,H57,H59,H61,H63,H65,H67,H69,H71,H73,H75,H77,H79,H81,H83,H85,H87,H89,H91,H93,H95,H97,H99	H21	=IF(E21="","",G21*E21)
I21,I23,I25,I27,I29,I31,I33,I35,I37,I39,I41,I43,I45,I47,I49,I51,I53,I55,I57,I59,I61,I63,I65,I67,I69,I71,I73,I75,I77,I79,I81,I83,I85,I87,I89,I91,I93,I95,I97,I99	I21	=IF(E21="","",E21*F21)
C21,C23,C25,C27,C29,C31,C33,C35,C37,C39,C41,C43,C45,C47,C49,C51,C53,C55,C57,C59,C61,C63,C65,C67,C69,C71,C73,C75,C77,C79,C81,C83,C85,C87,C89,C91,C93,C95,C97,C99	C21	=IF(B21="","","Thailand")
D21,D23,D25,D27,D29,D31,D33,D35,D37,D39,D41,D43,D45,D47,D49,D51,D53,D55,D57,D59,D61,D63,D65,D67,D69,D71,D73,D75,D77,D79,D81,D83,D85,D87,D89,D91,D93,D95,D97,D99	D21	=IFNA(VLOOKUP(B21,Item_List2,5,0),"")
B22,B100,B98,B96,B94,B92,B90,B88,B86,B84,B82,B80,B78,B76,B74,B72,B70,B68,B66,B64,B62,B60,B58,B56,B54,B52,B50,B48,B46,B44,B42,B40,B38,B36,B34,B32,B30,B28,B26,B24	B22	=IFNA(VLOOKUP(B21,Item_List2,3,0),"")
E103,I103	E103	=SUM(E20:E100)
I106	I106	=(SUBTOTAL(9,H20:H100)/1000)
D112	D112	=IF(B6="","",B6)

Named Ranges
Name	Refers To	Cells
Item_List	='Items List'!$A$2:$E$6	B22, B24, B26, B28, B30, B32, B34, B36, B38, B40, B42, B44, B46, B48, B50, B52, B54, B56, B58, B60, B62, B64, B66, B68, B70, B72, B74, B76, B78, B80, B82, B84, B86, B88, B90, B92, B94, B96, B98, B100

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6	Expression	=IF(ISBLANK(B6),TRUE)	text	NO
A3	Expression	=IF(ISBLANK(B3),TRUE)	text	NO
G20:G21,G23,G25,G27,G29,G31,G33,G35,G37,G39,G41,G43,G45,G47,G49,G51,G53,G55,G57,G59,G61,G63,G65,G67,G69,G71,G73,G75,G77,G79,G81,G83,G85,G87,G89,G91,G93,G95,G97,G99	Cell Value	<1	text	NO
F20:F21,F23,F25,F27,F29,F31,F33,F35,F37,F39,F41,F43,F45,F47,F49,F51,F53,F55,F57,F59,F61,F63,F65,F67,F69,F71,F73,F75,F77,F79,F81,F83,F85,F87,F89,F91,F93,F95,F97,F99	Cell Value	<0.01	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E6:I6	List	='Customer database'!$A$2:$A$1258
B21	List	='Items List'!$A$2:$A$6
B77	List	='Items List'!$A$2:$A$6
B23	List	='Items List'!$A$2:$A$6
B25	List	='Items List'!$A$2:$A$6
B53	List	='Items List'!$A$2:$A$6
B55	List	='Items List'!$A$2:$A$6
B57	List	='Items List'!$A$2:$A$6
B79	List	='Items List'!$A$2:$A$6
B81	List	='Items List'!$A$2:$A$6
B83	List	='Items List'!$A$2:$A$6
B85	List	='Items List'!$A$2:$A$6
B87	List	='Items List'!$A$2:$A$6
B89	List	='Items List'!$A$2:$A$6
B91	List	='Items List'!$A$2:$A$6
B93	List	='Items List'!$A$2:$A$6
B95	List	='Items List'!$A$2:$A$6
B97	List	='Items List'!$A$2:$A$6
B27	List	='Items List'!$A$2:$A$6
B29	List	='Items List'!$A$2:$A$6
B31	List	='Items List'!$A$2:$A$6
B33	List	='Items List'!$A$2:$A$6
B35	List	='Items List'!$A$2:$A$6
B37	List	='Items List'!$A$2:$A$6
B39	List	='Items List'!$A$2:$A$6
B41	List	='Items List'!$A$2:$A$6
B43	List	='Items List'!$A$2:$A$6
B45	List	='Items List'!$A$2:$A$6
B47	List	='Items List'!$A$2:$A$6
B49	List	='Items List'!$A$2:$A$6
B51	List	='Items List'!$A$2:$A$6
B59	List	='Items List'!$A$2:$A$6
B61	List	='Items List'!$A$2:$A$6
B63	List	='Items List'!$A$2:$A$6
B65	List	='Items List'!$A$2:$A$6
B67	List	='Items List'!$A$2:$A$6
B99	List	='Items List'!$A$2:$A$6
B71	List	='Items List'!$A$2:$A$6
B73	List	='Items List'!$A$2:$A$6
B75	List	='Items List'!$A$2:$A$6
B69	List	='Items List'!$A$2:$A$6