VBA to extract row data to sheet and update data set

[justatroy]

Hello All,

I created a Excel Workbook to enter and track quotes for something I am working on.. It works good to enter data, calculate additional charges ect and hold the quotes in a seperate sheet.

The sheet has a macro to copy from the input sheet to a sheet called database to hold all of the quote details, and another Macro to clear the values on the input sheet. I cant figure out how to pull the data back to the input sheet to update the record on the input sheet (search for a row on the database sheet and repopulate the input sheet and allow to update records)

Any suggestions on some VBA code or method to make this work for a novice

Sheet 1 = Input

Quote Tracker Ver2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		PROJECT INFORMATION
2
3
4		Date	7/19/2021
5		Submitter Name					Location:
6		Item #
7		Item Name
8		Item Description
9		Customer Details
10
11		Reference Number
12
13		Component Type					Tarif Rate (%)
14		Drawing #					Certification Charges ($)
15							Packaging Charges ($)
16		Supplier Name					Shipping Charge per ($ per lb/kg)
17		Supplier Location					Tooling Charge ($)
18
19
20
21
22
23		Pricing			Additional Fees						Additional Fees per UOM
24		EUA Quantity (annual)			Tarif Rate (%)				0%		Tarriff / Per UOM	$0.0000
25		Original Price (US$/unit)			Certification Charges ($)				$ -		Cert Charge/ Per UOM	$0.0000
26		Unit of Measure (UOM)			Packaging Charges ($)				$ -		Packaging Charge / Per UOM	$0.0000
27		Total w/out fees	-		Shipping Weight (lb/kg)						Shipping Charges /Per UOM	$0.0000
28					Shipping Charge per ($ per lb/kg)				$ -		Tooling Charges / Per UOM	$0.0000
29		Price With Fees (US$/unit)	$0.000		Tooling Charge ($)				$ -		Total Fees/ Per UOM	$0.0000
30		Order Total	$ -		Total Fees				$0.00
31
32
33		Quantity Break 1
34
35		Pricing			Additional Fees						Additional Fees per UOM
36		Quantity 1			Tarif Rate (%)				0%		Tarriff / Per UOM	$0.0000
37		Original Price (US$/unit)			Certification Charges ($)				$ -		Cert Charge/ Per UOM	$0.0000
38		Unit of Measure (UOM)			Packaging Charges ($)				$ -		Packaging Charge / Per UOM	$0.0000
39		Total w/out fees	-		Shipping Weight (lb/kg)						Shipping Charges /Per UOM	$0.0000
40					Shipping Charge per ($ per lb/kg)				$ -		Tooling Charges / Per UOM	$0.0000
41		Price With Fees (US$/unit)	$0.000		Tooling Charge ($)				$ -		Total Fees/ Per UOM	$0.0000
42		Order Total	0		Total Fees				$0.00
43
44
45		Quantity Break 2
46
47		Pricing			Additional Fees						Additional Fees per UOM
48		Quantity 2			Tarif Rate (%)				0%		Tarriff / Per UOM	$0.0000
49		Original Price (US$/unit)			Certification Charges ($)				$ -		Cert Charge/ Per UOM	$0.0000
50		Unit of Measure (UOM)			Packaging Charges ($)				$ -		Packaging Charge / Per UOM	$0.0000
51		Total w/out fees	-		Shipping Weight (lb/kg)						Shipping Charges /Per UOM	$0.0000
52					Shipping Charge per ($ per lb/kg)				$ -		Tooling Charges / Per UOM	$0.0000
53		Price With Fees (US$/unit)	$0.000		Tooling Charge ($)				$ -		Total Fees/ Per UOM	$0.0000
54		Order Total	0		Total Fees				$0.00
55
56
57		Quantity Break 3
58
59		Pricing			Additional Fees						Additional Fees per UOM
60		Quantity 3			Tarif Rate (%)				0%		Tarriff / Per UOM	$0.0000
61		Original Price (US$/unit)			Certification Charges ($)				$ -		Cert Charge/ Per UOM	$0.0000
62		Unit of Measure (UOM)			Packaging Charges ($)				$ -		Packaging Charge / Per UOM	$0.0000
63		Total w/out fees	-		Shipping Weight (lb/kg)						Shipping Charges /Per UOM	$0.0000
64					Shipping Charge per ($ per lb/kg)				$ -		Tooling Charges / Per UOM	$0.0000
65		Price With Fees (US$/unit)	$0.000		Tooling Charge ($)				$ -		Total Fees/ Per UOM	$0.0000
66		Order Total	0		Total Fees				$0.00
67
68
69		Quantity Break 4
70
71		Pricing			Additional Fees						Additional Fees per UOM
72		Quantity 4			Tarif Rate (%)				0%		Tarriff / Per UOM	$0.0000
73		Original Price (US$/unit)			Certification Charges ($)				$ -		Cert Charge/ Per UOM	$0.0000
74		Unit of Measure (UOM)			Packaging Charges ($)				$ -		Packaging Charge / Per UOM	$0.0000
75		Total w/out fees	-		Shipping Weight (lb/kg)						Shipping Charges /Per UOM	$0.0000
76					Shipping Charge per ($ per lb/kg)				$ -		Tooling Charges / Per UOM	$0.0000
77		Price With Fees (US$/unit)	$0.000		Tooling Charge ($)				$ -		Total Fees/ Per UOM	$0.0000
78		Order Total	0		Total Fees				$0.00
79
80
81		Quantity Break 5
82
83		Pricing			Additional Fees						Additional Fees per UOM
84		Quantity 5			Tarif Rate (%)				0%		Tarriff / Per UOM	$0.0000
85		Original Price (US$/unit)			Certification Charges ($)				$ -		Cert Charge/ Per UOM	$0.0000
86		Unit of Measure (UOM)			Packaging Charges ($)				$ -		Packaging Charge / Per UOM	$0.0000
87		Total w/out fees	-		Shipping Weight (lb/kg)						Shipping Charges /Per UOM	$0.0000
88					Shipping Charge per ($ per lb/kg)				$ -		Tooling Charges / Per UOM	$0.0000
89		Price With Fees (US$/unit)	$0.000		Tooling Charge ($)				$ -		Total Fees/ Per UOM	$0.0000
90		Order Total	0		Total Fees				$0.00
91
92
93
94
Input

Cell Formulas
Range	Formula
C4	C4	=NOW()
I24,I84,I72,I60,I48,I36	I24	=$I$13
I25,I85,I73,I61,I49,I37	I25	=$I$14
I26,I86,I74,I62,I50,I38	I26	=$I$15
C27,C87,C75,C63,C51,C39	C27	=C25*C24
L24,L84,L72,L60,L48,L36	L24	=IFERROR((C27*I24)/C24,0)
L25,L85,L73,L61,L49,L37	L25	=IFERROR(I25/C24,0)
L26,L86,L74,L62,L50,L38	L26	=IFERROR(I26/C24,0)
L27,L87,L75,L63,L51,L39	L27	=IFERROR((I28*I27)/C24,0)
L28,L88,L76,L64,L52,L40	L28	=IFERROR(I29/C24,0)
L29,L89,L77,L65,L53,L41	L29	=SUM(L24:L28)
C29,C89,C77,C65,C53,C41	C29	=IFERROR(C25+L29,0)
C30,C90,C78,C66,C54,C42	C30	=C24*C29
I28,I88,I76,I64,I52,I40	I28	=$I$16
I29,I89,I77,I65,I53,I41	I29	=$I$17
I30,I90,I78,I66,I54,I42	I30	=(C27*I24)+(I27*I28)+I25+I26+I29

SHEET2 = Database

Quote Tracker Ver2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY
1		Date	Submitter Name	Location:	Item #	Item Name		Item Description	Customer Details	Reference Number	Component Type (Manndrel, Wire)	Drawing #	Supplier Name	Supplier Location		EUA Quantity	EUA Quantity Original Price (US$/unit)	EUA Quantity Unit of Measure (UOM)	EUA Quantity Total w/out fees	EUA Quantity	EUA Quantity	EUA Quantity	EUA Quantity	EUA Quantity	EUA Quantity	EUA Quantity Total Fees	EUA Quantity Tarriff / Per UOM	EUA Quantity Cert Charge/ Per UOM	EUA Quantity Packaging Charge / Per UOM	EUA Quantity Shipping Charges /Per UOM	EUA Quantity Tooling Charges / Per UOM	EUA Quantity Price With Fees (US$/unit)	EUA Quantity Order Total	EUA Quantity Total Fees/ Per UOM	Quantity 1	Quantity 1 Original Price (US$/unit)	Quantity 1 Unit of Measure (UOM)	Quantity 1 Total w/out fees	Quantity 1	Quantity 1	Quantity 1	Quantity 1	Quantity 1	Quantity 1	Quantity 1 Total Fees	Quantity 1 Tarriff / Per UOM	Quantity 1 Cert Charge/ Per UOM	Quantity 1 Packaging Charge / Per UOM	Quantity 1 Shipping Charges /Per UOM	Quantity 1 Tooling Charges / Per UOM	Quantity 1 Total Fees/ Per UOM	Quantity 1 Price With Fees (US$/unit)	Quantity 1 Order Total	Quantity 2	Quantity 2 Original Price (US$/unit)	Quantity 2 Unit of Measure (UOM)	Quantity 2 Total w/out fees	Quantity 2	Quantity 2	Quantity 2	Quantity 2	Quantity 2	Quantity 2	Quantity 2 Total Fees	Quantity 2 Tarriff / Per UOM	Quantity 2 Cert Charge/ Per UOM	Quantity 2 Packaging Charge / Per UOM	Quantity 2 Shipping Charges /Per UOM	Quantity 2 Tooling Charges / Per UOM	Quantity 2 Total Fees/ Per UOM	Quantity 2 Price With Fees (US$/unit)	Quantity 2 Order Total	Quantity 3	Quantity 3 Original Price (US$/unit)	Quantity 3 Unit of Measure (UOM)	Quantity 3 Total w/out fees	Quantity 3	Quantity 3	Quantity 3	Quantity 3	Quantity 3	Quantity 3	Quantity 3 Total Fees	Quantity 3 Tarriff / Per UOM	Quantity 3 Cert Charge/ Per UOM	Quantity 3 Packaging Charge / Per UOM	Quantity 3 Shipping Charges /Per UOM	Quantity 3 Tooling Charges / Per UOM	Quantity 3 Total Fees/ Per UOM	Quantity 3 Price With Fees (US$/unit)	Quantity 3 Order Total	Quantity 4	Quantity 4 Original Price (US$/unit)	Quantity 4 Unit of Measure (UOM)	Quantity 4 Total w/out fees	Quantity 4	Quantity 4	Quantity 4	Quantity 4	Quantity 4	Quantity 4	Quantity 4 Total Fees	Quantity 4 Tarriff / Per UOM	Quantity 4 Cert Charge/ Per UOM	Quantity 4 Packaging Charge / Per UOM	Quantity 4 Shipping Charges /Per UOM	Quantity 4 Tooling Charges / Per UOM	Quantity 4 Total Fees/ Per UOM	Quantity 4 Price With Fees (US$/unit)	Quantity 4 Order Total	Quantity 5	Quantity 5 Original Price (US$/unit)	Quantity 5 Unit of Measure (UOM)	Quantity 5 Total w/out fees	Quantity 5	Quantity 5	Quantity 5	Quantity 5	Quantity 5	Quantity 5	Quantity 5 Total Fees	Quantity 5 Tarriff / Per UOM	Quantity 5 Cert Charge/ Per UOM	Quantity 5 Packaging Charge / Per UOM	Quantity 5 Shipping Charges /Per UOM	Quantity 5 Tooling Charges / Per UOM	Quantity 5 Total Fees/ Per UOM	Quantity 5 Price With Fees (US$/unit)	Quantity 5 Order Total
2
3		19-Jul-21	Mark	CT	Test-12345	Plastic Widget		test items #2	Test CO	XYZ1234-1	RG2345-1	RG2345-1	Amce Co. #2	NV	NV	$500,000	$0	ea	$100,000	$0	0	9-Apr-00	100	2.5	0	$ 350.00	$ -	$ -	$ 0.00	$0.00	$0.00	$0.00	$0.20	######	50000	1	ea	######	$0.00	$0.00	######	20	3	$0.00	######	$0.00	$0.00	$0.00	0.001	0	$0.00	$1.25	######	######	$1.00	ea	######	$0.00	$0.00	######	$40.00	$2.50	0	200	$0.00	$0.00	$0.00	$0.00	$0.00	0	1	######	######	$0.75	ea	#####	0	0	######	$60.00	$2.50	$0.00	#####	0	0	$0.00	$0.00	$0.00	$0.00	$0.75	2E+05	######	$0.55	ea	######	$0.00	$0.00	100	80	$2.50	$0.00	######	$0.00	$0.00	3E-04	0	$0.00	$0.00	$0.55	######	######	0.09	ea	######	$0.00	$0.00	######	######	2.5	0	######	$0.00	$0.00	$0.00	$0.00	0	0	$0.09	######
4		19-Jul-21	Fred	MN	12345-1	Widget - Test 1		Test Item#1	Company ZXY	X2468-1	XY123-1 Rev 2	XY123-1 Rev 2	Acme Co	CA	CA	$100,000	$1	ea	$100,000	$0	100	9-Apr-00	20	1	100	$ 25,320.00	$ 0.25	$ 0.00	$ 0.00	$0.00	$0.00	$0.25	$1.25	######	25000	5	ea	######	$0.25	######	######	4	1	######	######	$1.25	$0.00	$0.00	2E-04	0	$1.26	$6.26	######	######	$4.00		######	$0.25	######	######	$6.00	$1.00	100	50,306	$1.00	$0.00	$0.00	$0.00	$0.00	1.01	5	######	######	$3.75	ea	#####	0.25	100	######	$8.00	$1.00	######	#####	0.94	0	$0.00	$0.00	$0.00	$0.94	$4.69	3E+05	80,000	$2.00	ea	######	$0.25	######	100	12	$1.00	######	######	$0.50	$0.00	0.001	0	$0.00	$0.50	$2.50	######	######	1.5	ea	######	$0.25	######	######	$16.00	1	100	######	$0.38	$0.00	$0.00	$0.00	0.001	0	$1.88	######
Database

Please Help

Troy