Running a Find Macro Then Copy/Paste Corresponding Data

[JML0328]

I'm a VBA beginner so please excuse my ignorance.

I am building a purchasing system that can generate orders or requests for quotes.

I would like to write a macro on my "quote entry" sheet that finds a saved quote on the "saved quotes" sheet (by quote#) and places that quote's information back in the "quote entry" sheet.

So, the macro would search for the number placed in the cell, find it in another worksheet, select the corresponding cells (that apply to that quote#), copy them, and paste them back in the entry sheet.

I tried searching the forum, the internet, and to no avail. If you have any insight, please help and thanks for your time.

 

[VoG]

You might be able to use VLOOKUP formulas for this - see http://www.contextures.com/xlFunctions02.html

 

[JML0328]

I will give that a try, thanks.

 

[VoG]

If you want to post images of your sheets you can use Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Obviously, fictionalise the data if it is sensitive.

 

[JML0328]

Quote Entry

  ABCDEFGHIJKLM1Quote Entry Sheet            2Use this tab to enter your Request for Quote    Retrieve Quote #(INSERT QUOTE # HERE)   3             4 RFQ Number  LineITEM/PART #DESCRIPTIONQTYU/MUNIT PRICE 5 Job/Cost Code  1      6 Ship to Attn  2      7 Ship to Company Name  3      8 Ship to Street Address  4      9 Ship to City, State, and Zip  5      10 Ship to Phone#  6      11    7      12 Vendor Name  8      13 Vendor Street Address  9      14 Vendor City State Zip  10      15 Vendor Phone  11      16 Vendor Email/Website  12      17    13      18 Shipping Carrier w/ Method  14      19 F.O.B.  15      20 Shipping Terms Internal Notes (will not show up in the RFQ) 21 Shipping Cost   22 Other Cost  23 Extra Comments  24             25             26             27              

Excel tables to the web >>  Excel Jeanie HTML 4

This is the quote entry sheet. I would like to type the quote number in cell I2 and hit a button where the macro would go onto the "saved quotes" page, find the quote, copy all the information for that quote and paste it back in the quote entry sheet.

Saved Quotes

  ABCDEFGHIJKL1Saved Quotes           2Use this sheet to save quotes, to later convert to orders.           3            4            5 Quote Date7/23/2010         6 RFQ Number1001 LineITEM/PART #DESCRIPTIONQTYU/MUNIT PRICE7 Job/Cost CodeT 1Q5trgLaptop Battery4EA45.368 Ship to AttnGilbert 2T7opoCar Battery1EA27.649 Ship to Company NameEast Canton Gas Co. 3W967Battery Charger6EA14.4710 Ship to Street Address450 E Smith St 4     11 Ship to City, State, and ZipEast Canton, OH 44616 5     12 Ship to Phone#330-458-1298 6     13    7     14 Vendor NameHanson & Sons 8     15 Vendor Street Address125 East Chestnut St 9     16 Vendor City State ZipBoston, MA 08966 10     17 Vendor Phone540-258-4747 11     18 Vendor Email/Websitewww.hansons.com 12     19    13     20 Shipping Carrier w/ MethodUPS RED 14     21 F.O.B.SHIP COLLECT 15     22 Shipping TermsCollect# 675245Internal Notes (will not show up in the RFQ)23 Shipping Cost19.63 24 Other Cost025 Extra Comments 26            27            28 Quote Date7/20/2010         29 RFQ Number1 LineITEM/PART #DESCRIPTIONQTYU/MUNIT PRICE30 Job/Cost CodeQ 1Q5trgLaptop Battery2EA45.3631 Ship to AttnGilbert 2T7opoCar Battery3EA27.64 

Excel tables to the web >>  Excel Jeanie HTML 4

Quote Entry

  ABCDEFGHIJKL1Quote Entry Sheet           2Use this tab to enter your Request for Quote    Retrieve Quote #(INSERT QUOTE # HERE)  3            4 RFQ Number1001 LineITEM/PART #DESCRIPTIONQTYU/MUNIT PRICE5 Job/Cost CodeT 1Q5trgLaptop Battery4EA45.366 Ship to AttnGilbert 2T7opoCar Battery1EA27.647 Ship to Company NameEast Canton Gas Co. 3W967Battery Charger6EA14.478 Ship to Street Address450 E Smith St 4     9 Ship to City, State, and ZipEast Canton, OH 44616 5     10 Ship to Phone#330-458-1298 6     11    7     12 Vendor NameHanson & Sons 8     13 Vendor Street Address125 East Chestnut St 9     14 Vendor City State ZipBoston, MA 08966 10     15 Vendor Phone540-258-4747 11     16 Vendor Email/Websitewww.hansons.com 12     17    13     18 Shipping Carrier w/ MethodUPS RED 14     19 F.O.B.SHIP COLLECT 15     20 Shipping TermsCollect# 675245Internal Notes (will not show up in the RFQ)21 Shipping Cost19.63 22 Other Cost023 Extra Comments 24            25            26            27            28             

Excel tables to the web >>  Excel Jeanie HTML 4

I think VLOOKUP is a good start to tracking down what I'm looking to do, now I just need to make sure it will copy/paste the proper corresponding data.

 

[VoG]

When using Excel Jeanie do not enclose the code in code tags. Just paste into the thread. Example

Excel Workbook
	A	B	C
1	124	-984	1835
2	198	-1047	1846
3	131	-1829	98
Sheet2

There is a test are of the board that you can practise in http://www.mrexcel.com/forum/forumdisplay.php?f=14

 

[JML0328]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Quote Entry Sheet
2	Use this tab to enter your Request for Quote					Retrieve Quote #		(INSERT QUOTE # HERE)
3
4		RFQ Number			Line	ITEM/PART #	DESCRIPTION			QTY	U/M	UNIT PRICE
5		Job/Cost Code			1
6		Ship to Attn			2
7		Ship to Company Name			3
8		Ship to Street Address			4
9		Ship to City, State, and Zip			5
10		Ship to Phone#			6
11					7
12		Vendor Name			8
13		Vendor Street Address			9
14		Vendor City State Zip			10
15		Vendor Phone			11
16		Vendor Email/Website			12
17					13
18		Shipping Carrier w/ Method			14
19		F.O.B.			15
20		Shipping Terms		Internal Notes (will not show up in the RFQ)
21		Shipping Cost
22		Other Cost
23		Extra Comments
24
25
26
27
28
Quote Entry

This is the quote entry sheet. I would like to type the quote number in cell I2 and hit a button where the macro would go onto the "saved quotes" page, find the quote, copy all the information for that quote and paste it back in the quote entry sheet.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Saved Quotes
2	Use this sheet to save quotes, to later convert to orders.
3
4
5		Quote Date	7/23/2010
6		RFQ Number	1001		Line	ITEM/PART #	DESCRIPTION			QTY	U/M	UNIT PRICE
7		Job/Cost Code	T		1	Q5trg	Laptop Battery			4	EA	45.36
8		Ship to Attn	Gilbert		2	T7opo	Car Battery			1	EA	27.64
9		Ship to Company Name	East Canton Gas Co.		3	W967	Battery Charger			6	EA	14.47
10		Ship to Street Address	450 E Smith St		4
11		Ship to City, State, and Zip	East Canton, OH 44616		5
12		Ship to Phone#	330-458-1298		6
13					7
14		Vendor Name	Hanson & Sons		8
15		Vendor Street Address	125 East Chestnut St		9
16		Vendor City State Zip	Boston, MA 08966		10
17		Vendor Phone	540-258-4747		11
18		Vendor Email/Website	www.hansons.com		12
19					13
20		Shipping Carrier w/ Method	UPS RED		14
21		F.O.B.	SHIP COLLECT		15
22		Shipping Terms	Collect# 675245	Internal Notes (will not show up in the RFQ)
23		Shipping Cost	19.63
24		Other Cost	0
25		Extra Comments
26
27
28		Quote Date	7/20/2010
29		RFQ Number	1		Line	ITEM/PART #	DESCRIPTION			QTY	U/M	UNIT PRICE
30		Job/Cost Code	Q		1	Q5trg	Laptop Battery			2	EA	45.36
Saved Quotes

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Quote Entry Sheet
2	Use this tab to enter your Request for Quote					Retrieve Quote #		(INSERT QUOTE # HERE)
3
4		RFQ Number	1001		Line	ITEM/PART #	DESCRIPTION			QTY	U/M	UNIT PRICE
5		Job/Cost Code	T		1	Q5trg	Laptop Battery			4	EA	45.36
6		Ship to Attn	Gilbert		2	T7opo	Car Battery			1	EA	27.64
7		Ship to Company Name	East Canton Gas Co.		3	W967	Battery Charger			6	EA	14.47
8		Ship to Street Address	450 E Smith St		4
9		Ship to City, State, and Zip	East Canton, OH 44616		5
10		Ship to Phone#	330-458-1298		6
11					7
12		Vendor Name	Hanson & Sons		8
13		Vendor Street Address	125 East Chestnut St		9
14		Vendor City State Zip	Boston, MA 08966		10
15		Vendor Phone	540-258-4747		11
16		Vendor Email/Website	www.hansons.com		12
17					13
18		Shipping Carrier w/ Method	UPS RED		14
19		F.O.B.	SHIP COLLECT		15
20		Shipping Terms	Collect# 675245	Internal Notes (will not show up in the RFQ)
21		Shipping Cost	19.63
22		Other Cost	0
23		Extra Comments
24
25
26
27
28
Quote Entry

I think VLOOKUP is a good start to tracking down what I'm looking to do, now I just need to make sure it will copy/paste the proper corresponding data.