Two workbooks - Find matching cell value on another workbook, then copy paste cell info into same row of other workbook in specified collum

[pulsenation]

I have a master workbook full of data, and each row represents a case. Every day a new row of data is populated, with collum A being used for the unique case number, however collum Q is left blank until the following day once a reference number has been generated seperetly to be input in to it.

I would like to have a seperate local workbook, to input the case number in a cell (F7) which will then search collum A of the master workbook that holds the data. Once it has found the case number, copy the information from the local workbook in cell I7 and paste it into collum Q of the same row of the master workbook that corresponds with the case number.

I have seen similar in the forums, but nothing specfic to my needs.

I am looking for a VBA script that will perform the above, with the vba being done on the local workbook from the press of a button.

 

[alansidman]

Using XL2BB upload a sample file that is representative of your actual file. Also show a mocked up solution so that we can work with exactly what you need.

 

[pulsenation]

Using XL2BB upload a sample file that is representative of your actual file. Also show a mocked up solution so that we can work with exactly what you need.

I have two worlkbooks. A main database which will remain closed, and only automatically open and close when the button is pushed in the search and insert file.

I would put a number in cell F7 of the search and insert file to represent the segment then put the order number in cell I7. When I press the button, I want it to find the segment on the master database, copy cell I7 and paste the order number in collum Q of the main database matching the row of the segment it found.

I can not find a VBA script to run as a macro from the button within the search and insert file.

Hope that makes a little more sense now. The below are for demo use, and not real life data.

Main Database.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Segment	Country	Product	Discount Band	Units Sold	Manufacturing Price	Sale Price	Gross Sales	Discounts	Sales	COGS	Profit	Date	Month Number	Month Name	Year	Order Number
2	1	Canada	Carretera	None	1618.5	$ 3.00	$ 20.00	$ 32,370.00	$ -	$ 32,370.00	$16,185.00	$ 16,185.00	01/01/2014	1	January	2014
3	2	Germany	Carretera	None	1321	$ 3.00	$ 20.00	$ 26,420.00	$ -	$ 26,420.00	$13,210.00	$ 13,210.00	01/01/2014	1	January	2014
4	3	France	Carretera	None	2178	$ 3.00	$ 15.00	$ 32,670.00	$ -	$ 32,670.00	$21,780.00	$ 10,890.00	01/06/2014	6	June	2014
5	4	Germany	Carretera	None	888	$ 3.00	$ 15.00	$ 13,320.00	$ -	$ 13,320.00	$ 8,880.00	$ 4,440.00	01/06/2014	6	June	2014
6	5	Mexico	Carretera	None	2470	$ 3.00	$ 15.00	$ 37,050.00	$ -	$ 37,050.00	$24,700.00	$ 12,350.00	01/06/2014	6	June	2014
7	6	Germany	Carretera	None	1513	$ 3.00	$ 350.00	$ 529,550.00	$ -	$ 529,550.00	##########	$ 136,170.00	01/12/2014	12	December	2014
8	7	Germany	Montana	None	921	$ 5.00	$ 15.00	$ 13,815.00	$ -	$ 13,815.00	$ 9,210.00	$ 4,605.00	01/03/2014	3	March	2014
9	8	Canada	Montana	None	2518	$ 5.00	$ 12.00	$ 30,216.00	$ -	$ 30,216.00	$ 7,554.00	$ 22,662.00	01/06/2014	6	June	2014
10	9	France	Montana	None	1899	$ 5.00	$ 20.00	$ 37,980.00	$ -	$ 37,980.00	$18,990.00	$ 18,990.00	01/06/2014	6	June	2014
11	10	Germany	Montana	None	1545	$ 5.00	$ 12.00	$ 18,540.00	$ -	$ 18,540.00	$ 4,635.00	$ 13,905.00	01/06/2014	6	June	2014
12	11	Mexico	Montana	None	2470	$ 5.00	$ 15.00	$ 37,050.00	$ -	$ 37,050.00	$24,700.00	$ 12,350.00	01/06/2014	6	June	2014
13	12	Canada	Montana	None	2665.5	$ 5.00	$ 125.00	$ 333,187.50	$ -	$ 333,187.50	##########	$ 13,327.50	01/07/2014	7	July	2014
14	13	Mexico	Montana	None	958	$ 5.00	$ 300.00	$ 287,400.00	$ -	$ 287,400.00	##########	$ 47,900.00	01/08/2014	8	August	2014
15	14	Germany	Montana	None	2146	$ 5.00	$ 7.00	$ 15,022.00	$ -	$ 15,022.00	$10,730.00	$ 4,292.00	01/09/2014	9	September	2014
16	15	Canada	Montana	None	345	$ 5.00	$ 125.00	$ 43,125.00	$ -	$ 43,125.00	$41,400.00	$ 1,725.00	01/10/2013	10	October	2013
17	16	United States of America	Montana	None	615	$ 5.00	$ 15.00	$ 9,225.00	$ -	$ 9,225.00	$ 6,150.00	$ 3,075.00	01/12/2014	12	December	2014
18	17	Canada	Paseo	None	292	$ 10.00	$ 20.00	$ 5,840.00	$ -	$ 5,840.00	$ 2,920.00	$ 2,920.00	01/02/2014	2	February	2014
19	18	Mexico	Paseo	None	974	$ 10.00	$ 15.00	$ 14,610.00	$ -	$ 14,610.00	$ 9,740.00	$ 4,870.00	01/02/2014	2	February	2014
20	19	Canada	Paseo	None	2518	$ 10.00	$ 12.00	$ 30,216.00	$ -	$ 30,216.00	$ 7,554.00	$ 22,662.00	01/06/2014	6	June	2014
21	20	Germany	Paseo	None	1006	$ 10.00	$ 350.00	$ 352,100.00	$ -	$ 352,100.00	##########	$ 90,540.00	01/06/2014	6	June	2014
22	21	Germany	Paseo	None	367	$ 10.00	$ 12.00	$ 4,404.00	$ -	$ 4,404.00	$ 1,101.00	$ 3,303.00	01/07/2014	7	July	2014
23	22	Mexico	Paseo	None	883	$ 10.00	$ 7.00	$ 6,181.00	$ -	$ 6,181.00	$ 4,415.00	$ 1,766.00	01/08/2014	8	August	2014
24	23	France	Paseo	None	549	$ 10.00	$ 15.00	$ 8,235.00	$ -	$ 8,235.00	$ 5,490.00	$ 2,745.00	01/09/2013	9	September	2013
25	24	Mexico	Paseo	None	788	$ 10.00	$ 300.00	$ 236,400.00	$ -	$ 236,400.00	##########	$ 39,400.00	01/09/2013	9	September	2013
26	25	Mexico	Paseo	None	2472	$ 10.00	$ 15.00	$ 37,080.00	$ -	$ 37,080.00	$24,720.00	$ 12,360.00	01/09/2014	9	September	2014
27	26	United States of America	Paseo	None	1143	$ 10.00	$ 7.00	$ 8,001.00	$ -	$ 8,001.00	$ 5,715.00	$ 2,286.00	01/10/2014	10	October	2014
28	27	Canada	Paseo	None	1725	$ 10.00	$ 350.00	$ 603,750.00	$ -	$ 603,750.00	##########	$ 155,250.00	01/11/2013	11	November	2013
29	28	United States of America	Paseo	None	912	$ 10.00	$ 12.00	$ 10,944.00	$ -	$ 10,944.00	$ 2,736.00	$ 8,208.00	01/11/2013	11	November	2013
30	29	Canada	Paseo	None	2152	$ 10.00	$ 15.00	$ 32,280.00	$ -	$ 32,280.00	$21,520.00	$ 10,760.00	01/12/2013	12	December	2013
31	30	Canada	Paseo	None	1817	$ 10.00	$ 20.00	$ 36,340.00	$ -	$ 36,340.00	$18,170.00	$ 18,170.00	01/12/2014	12	December	2014
32	31	Germany	Paseo	None	1513	$ 10.00	$ 350.00	$ 529,550.00	$ -	$ 529,550.00	##########	$ 136,170.00	01/12/2014	12	December	2014
33	32	Mexico	Velo	None	1493	$ 120.00	$ 7.00	$ 10,451.00	$ -	$ 10,451.00	$ 7,465.00	$ 2,986.00	01/01/2014	1	January	2014
34	33	France	Velo	None	1804	$ 120.00	$ 125.00	$ 225,500.00	$ -	$ 225,500.00	##########	$ 9,020.00	01/02/2014	2	February	2014
35	34	Germany	Velo	None	2161	$ 120.00	$ 12.00	$ 25,932.00	$ -	$ 25,932.00	$ 6,483.00	$ 19,449.00	01/03/2014	3	March	2014
36	35	Germany	Velo	None	1006	$ 120.00	$ 350.00	$ 352,100.00	$ -	$ 352,100.00	##########	$ 90,540.00	01/06/2014	6	June	2014
37	36	Germany	Velo	None	1545	$ 120.00	$ 12.00	$ 18,540.00	$ -	$ 18,540.00	$ 4,635.00	$ 13,905.00	01/06/2014	6	June	2014
Sheet1

Search and insert.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5
6						Segment			Order Number
7
8
9
10
11
12
13
14
Sheet1

 

[alansidman]

If you are willing to move away from VBA, I will demonstrate a Power Query Solution that employs a parameter query.

 

[pulsenation]

If you are willing to move away from VBA, I will demonstrate a Power Query Solution that employs a parameter query.

By all means, if you think you can accommodate my requirement. I am very new to creating backend programming, normally work on front end formulas but willing to learn any new techniques.

 

[alansidman]

I have uploaded a file to Box.Net

PQ Segment.xlsm | Powered by Box

app.box.com

Here is a tutorial that shows the steps I took to create the file in Box.net.