VBA / Macro Help

[clappl001]

Hello,

I am working on a workbook that has an ordering sheet, and then an invoice page that is populated depending on the data entered into the ordering sheet. I want the invoice sheet to expand and contract depending on how many entries are placed on the ordering sheet. I had it doing it through a power query but then if i wanted to have a footer, each time i refreshed the query it would delete the footer if the new data in the order sheet went below where the footer was.

I can't seem to get the sheets to properly upload using xl2bb sorry.

 

[clappl001]

Oh and also i am hoping to maintain some level of formatting both on the invoice table and the footer of the invoice (which will contain payment terms as well as the totals) so that is another issue that i am struggling to solve.

 

[Logit]

You will receive a faster answer if you post your workbook to a download site.

 

[Peter_SSs]

I can't seem to get the sheets to properly upload using xl2bb sorry.

What goes wrong and at what point of the process does it go wrong?

 

[clappl001]

What goes wrong and at what point of the process does it go wrong?

ok i just realized i was not using it correctly, here are the three sheets i am working with

2025 Parts Table for MG Kits copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Glide Screen Parts Order Sheet				Date of Entry:	November 19, 2024
2	1.	Select Parts Category in Column "B".			Order #:	slkdfd
3	2.	Select Part Name in Column "D".			Ship To:
4	3.	Chose Colour-Select in Column "G".			Document:
5	4.	Insert Quantity in Column "I".
6					Contact:
7
8
9
10						Colour
11		Parts Category	SKU#	Part Name	Image	Available	Select	Material	QTY	Unit	Total
12		PleatedMesh	243442-2.45	Pleated Mesh 2450 pleat width 25mm, MOQ 150m, 20x20.		Black	Black	Polyester	10	$11.69	$116.90
13
14
15
16
17
18
19
20
21
Order Sheet

Cell Formulas
Range	Formula
E12:E21	E12	=IF(D12="","",XLOOKUP( D12, Images!E:E,Images!D:D, ""))
F12:F21	F12	=IF(D12="","",XLOOKUP( D12, Images!E:E,Images!F:F, ""))
J12:J21	J12	=IF(D12="","",XLOOKUP( D12, Images!E:E,Images!H:H, ""))
K12:K21	K12	=IF(I12="", "", IFERROR(J12*I12, 0))
C12:C21	C12	=IF(D12="","",XLOOKUP( D12, Images!E:E,Images!B:B, ""))
H12:H21	H12	=IF(D12="","",XLOOKUP( D12, Images!E:E,Images!G:G, ""))

Named Ranges
Name	Refers To	Cells
Anchor_Base_MG__L_R___Includes_a_square_nut_M4	=Images!$D$13	E12:E21
Bottom_Hook_MG__option	=Images!$D$28	E12:E21
Bottom_Rail_15	=Images!$D$59	E12:E21
Bottom_rail_angle_spacer__option	=Images!$D$38	E12:E21
Bottom_rail_end_MG__option	=Images!$D$30	E12:E21
Bottom_Rail_Joint_MG	=Images!$D$19	E12:E21
Catch_Frame_11.5	=Images!$D$55	E12:E21
Catch_Frame_Cover	=Images!$D$8	E12:E21
Catch_Frame_End_MG	=Images!$D$24	E12:E21
Corner_Joint_1450___for_FMA____option	=Images!$D$32	E12:E21
Double_sided_foam_tape__for_Top_rail___Housing___Catch_frame_____1005__W10mm）_option	=Images!$D$42	E12:E21
Double_sided_tape__for_Mesh_Strip_23_MG______5600__W20mm	=Images!$D$43	E12:E21
Dust_cover__option	=Images!$D$37	E12:E21
Face_Mount_Attachment	=Images!$D$61	E12:E21
Flat_head_screw____for_Wire_Anchor	=Images!$D$45	E12:E21
Flat_Installation_screw____for_Bottom_rail	=Images!$D$48	E12:E21
FMA_center_joint__option	=Images!$D$33	E12:E21
Handle_MG____With_Handle_Base_Flathead_Tapping_Screw_3x6.5	=Images!$D$27	E12:E21
Hexagon_wrench__2.5mm	=Images!$D$49	E12:E21
Housing__44	=Images!$D$53	E12:E21
Housing_End_MG	=Images!$D$23	E12:E21
Inner_Cartridge_31.8	=Images!$D$50	E12:E21
Magnet_strip___for_Sliding_bar_and_Catch_frame___600GS	=Images!$D$7	E12:E21
Mesh_Holder_End_MG	=Images!$D$22	E12:E21
Mesh_Strip_23_MG____With_Double_sided_tape__5600_W_20mm	=Images!$D$11	E12:E21
MG_FMA_End_Cap__R_L____option	=Images!$D$34	E12:E21
Mo_Hair__option	=Images!$D$39	E12:E21
Pleated_Mesh_2400__pleat_width_25_mm	=Images!$D$2	E12:E21
Screw_Pan_TPB____for_End_caps	=Images!$D$46	E12:E21
Sliding_bar_51	=Images!$D$51	E12:E21
Sliding_Bar_Bottom_Cap_Assy__MG___With_Roller_P_Roller_M_Shaft_2.2x12mm_Wheel_8.5mm_Shaft_2x6.8mm	=Images!$D$21	E12:E21
Sliding_Bar_Holder_MG__option___With_Double_sided_tape__5760_W_10mm	=Images!$D$29	E12:E21
Sliding_Bar_Top_Cap_Assy__MG___With_Roller_P_Roller_M_Shaft_2.2x12mm	=Images!$D$20	E12:E21
Tapping_head_installation_screw	=Images!$D$47	E12:E21
Tension_Adjuster_MG__Includes_a_square_nut_M4	=Images!$D$26	E12:E21
Tesa_tape___for_Bottom_rail___W_25MM__option	=Images!$D$40	E12:E21
Tesa_tape___for_Transition___W_14MM__option	=Images!$D$41	E12:E21
Top_Rail_60	=Images!$D$57	E12:E21
Top_rail_end_MG__option	=Images!$D$31	E12:E21
Top_Rail_Joint_MG	=Images!$D$18	E12:E21
Transition__option	=Images!$D$35	E12:E21
Transition_end_cap__L_R___option	=Images!$D$36	E12:E21
Trus_head_screw____for_Tension_Adjuster	=Images!$D$44	E12:E21
Wire	=Images!$D$6	E12:E21
Wire_Anchor_MG__L_R	=Images!$D$14	E12:E21
Wire_End_Adjuster_MG__L_R___Includes_a_square_nut_M5__for_Bottom_Rail	=Images!$D$15	E12:E21
Wire_End_Adjuster_T__Includes_a_square_nut_M5__for_Top_Rail	=Images!$D$16	E12:E21
Wire_End_Hook_MG__SUS	=Images!$D$17	E12:E21
Wire_Guide_assy_MG___With_Roller_P_Roller_M_Shaft_2.2x12mm	=Images!$D$25	E12:E21

Cells with Data Validation
Cell	Allow	Criteria
G12:G21	List	=INDIRECT(F12)
D12:D21	List	=INDIRECT(B12)
B12:B21	List	=LISTS!$A$1:$I$1
F4:H4	List	=LISTS!$N$2:$N$9

2025 Parts Table for MG Kits copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4
5
6
7
8
9	Customer:		Invoice:
10	Address:
11	Contact:
12	Tel:
13	Email:
14	PO#:
15	Date:
16
17		PartName	SKUNumber	Colour	Qty	UnitPrice	TotalPrice
18
19
20						Subtotal
21						Discount
22						Tax
23						Subtotal
24						Freight
25						Total
26
27
28
29
30
31
32
33
Invoice

2025 Parts Table for MG Kits copy.xlsm
	A	B	C	D	E	F	G
1	PartName	SKUNumber	Colour	Qty	UnitPrice	TotalPrice
2	Pleated Mesh 2450 pleat width 25mm, MOQ 150m, 20x20.	243442-2.45	Black	10	11.69	116.9
3	0		0	0
4	0		0	0
5	0		0	0
6	0		0	0
7	0		0	0
8	0		0	0
9	0		0	0
10	0		0	0
11	0		0	0
12	0		0	0
13	0		0	0
14	0		0	0
15	0		0	0
16	0		0	0
17	0		0	0
18	0		0	0
19	0		0	0
20	0		0	0
21	0		0	0
22	0		0	0
23	0		0	0
24	0		0	0
25	0		0	0
26	0		0	0
27	0		0	0
28	0		0	0
29	0		0	0
30	0		0	0
31	0		0	0
32	0		0	0
33	0		0	0
34	0		0	0
35	0		0	0
36	0		0	0
OrderData

Cell Formulas
Range	Formula
A2:A36	A2	='Order Sheet'!D12
B2:B36	B2	='Order Sheet'!C12
C2:C36	C2	='Order Sheet'!G12
D2:F36	D2	='Order Sheet'!I12