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

pulsenation

New Member
Joined
Feb 11, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,228
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 11, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQ
1SegmentCountry Product Discount Band Units Sold Manufacturing Price Sale Price Gross Sales Discounts Sales COGS Profit DateMonth Number Month Name Year Order Number
21Canada Carretera None 1618.5$ 3.00$ 20.00$ 32,370.00$ -$ 32,370.00$16,185.00$ 16,185.0001/01/20141 January 2014
32Germany Carretera None 1321$ 3.00$ 20.00$ 26,420.00$ -$ 26,420.00$13,210.00$ 13,210.0001/01/20141 January 2014
43France Carretera None 2178$ 3.00$ 15.00$ 32,670.00$ -$ 32,670.00$21,780.00$ 10,890.0001/06/20146 June 2014
54Germany Carretera None 888$ 3.00$ 15.00$ 13,320.00$ -$ 13,320.00$ 8,880.00$ 4,440.0001/06/20146 June 2014
65Mexico Carretera None 2470$ 3.00$ 15.00$ 37,050.00$ -$ 37,050.00$24,700.00$ 12,350.0001/06/20146 June 2014
76Germany Carretera None 1513$ 3.00$ 350.00$ 529,550.00$ -$ 529,550.00##########$ 136,170.0001/12/201412 December 2014
87Germany Montana None 921$ 5.00$ 15.00$ 13,815.00$ -$ 13,815.00$ 9,210.00$ 4,605.0001/03/20143 March 2014
98Canada Montana None 2518$ 5.00$ 12.00$ 30,216.00$ -$ 30,216.00$ 7,554.00$ 22,662.0001/06/20146 June 2014
109France Montana None 1899$ 5.00$ 20.00$ 37,980.00$ -$ 37,980.00$18,990.00$ 18,990.0001/06/20146 June 2014
1110Germany Montana None 1545$ 5.00$ 12.00$ 18,540.00$ -$ 18,540.00$ 4,635.00$ 13,905.0001/06/20146 June 2014
1211Mexico Montana None 2470$ 5.00$ 15.00$ 37,050.00$ -$ 37,050.00$24,700.00$ 12,350.0001/06/20146 June 2014
1312Canada Montana None 2665.5$ 5.00$ 125.00$ 333,187.50$ -$ 333,187.50##########$ 13,327.5001/07/20147 July 2014
1413Mexico Montana None 958$ 5.00$ 300.00$ 287,400.00$ -$ 287,400.00##########$ 47,900.0001/08/20148 August 2014
1514Germany Montana None 2146$ 5.00$ 7.00$ 15,022.00$ -$ 15,022.00$10,730.00$ 4,292.0001/09/20149 September 2014
1615Canada Montana None 345$ 5.00$ 125.00$ 43,125.00$ -$ 43,125.00$41,400.00$ 1,725.0001/10/201310 October 2013
1716United States of America Montana None 615$ 5.00$ 15.00$ 9,225.00$ -$ 9,225.00$ 6,150.00$ 3,075.0001/12/201412 December 2014
1817Canada Paseo None 292$ 10.00$ 20.00$ 5,840.00$ -$ 5,840.00$ 2,920.00$ 2,920.0001/02/20142 February 2014
1918Mexico Paseo None 974$ 10.00$ 15.00$ 14,610.00$ -$ 14,610.00$ 9,740.00$ 4,870.0001/02/20142 February 2014
2019Canada Paseo None 2518$ 10.00$ 12.00$ 30,216.00$ -$ 30,216.00$ 7,554.00$ 22,662.0001/06/20146 June 2014
2120Germany Paseo None 1006$ 10.00$ 350.00$ 352,100.00$ -$ 352,100.00##########$ 90,540.0001/06/20146 June 2014
2221Germany Paseo None 367$ 10.00$ 12.00$ 4,404.00$ -$ 4,404.00$ 1,101.00$ 3,303.0001/07/20147 July 2014
2322Mexico Paseo None 883$ 10.00$ 7.00$ 6,181.00$ -$ 6,181.00$ 4,415.00$ 1,766.0001/08/20148 August 2014
2423France Paseo None 549$ 10.00$ 15.00$ 8,235.00$ -$ 8,235.00$ 5,490.00$ 2,745.0001/09/20139 September 2013
2524Mexico Paseo None 788$ 10.00$ 300.00$ 236,400.00$ -$ 236,400.00##########$ 39,400.0001/09/20139 September 2013
2625Mexico Paseo None 2472$ 10.00$ 15.00$ 37,080.00$ -$ 37,080.00$24,720.00$ 12,360.0001/09/20149 September 2014
2726United States of America Paseo None 1143$ 10.00$ 7.00$ 8,001.00$ -$ 8,001.00$ 5,715.00$ 2,286.0001/10/201410 October 2014
2827Canada Paseo None 1725$ 10.00$ 350.00$ 603,750.00$ -$ 603,750.00##########$ 155,250.0001/11/201311 November 2013
2928United States of America Paseo None 912$ 10.00$ 12.00$ 10,944.00$ -$ 10,944.00$ 2,736.00$ 8,208.0001/11/201311 November 2013
3029Canada Paseo None 2152$ 10.00$ 15.00$ 32,280.00$ -$ 32,280.00$21,520.00$ 10,760.0001/12/201312 December 2013
3130Canada Paseo None 1817$ 10.00$ 20.00$ 36,340.00$ -$ 36,340.00$18,170.00$ 18,170.0001/12/201412 December 2014
3231Germany Paseo None 1513$ 10.00$ 350.00$ 529,550.00$ -$ 529,550.00##########$ 136,170.0001/12/201412 December 2014
3332Mexico Velo None 1493$ 120.00$ 7.00$ 10,451.00$ -$ 10,451.00$ 7,465.00$ 2,986.0001/01/20141 January 2014
3433France Velo None 1804$ 120.00$ 125.00$ 225,500.00$ -$ 225,500.00##########$ 9,020.0001/02/20142 February 2014
3534Germany Velo None 2161$ 120.00$ 12.00$ 25,932.00$ -$ 25,932.00$ 6,483.00$ 19,449.0001/03/20143 March 2014
3635Germany Velo None 1006$ 120.00$ 350.00$ 352,100.00$ -$ 352,100.00##########$ 90,540.0001/06/20146 June 2014
3736Germany Velo None 1545$ 120.00$ 12.00$ 18,540.00$ -$ 18,540.00$ 4,635.00$ 13,905.0001/06/20146 June 2014
Sheet1


Search and insert.xlsm
ABCDEFGHIJKLM
1
2
3
4
5
6SegmentOrder Number
7
8
9
10
11
12
13
14
Sheet1
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,228
Office Version
  1. 365
Platform
  1. Windows
If you are willing to move away from VBA, I will demonstrate a Power Query Solution that employs a parameter query.
 

pulsenation

New Member
Joined
Feb 11, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,228
Office Version
  1. 365
Platform
  1. Windows
I have uploaded a file to Box.Net


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

Watch MrExcel Video

Forum statistics

Threads
1,132,781
Messages
5,655,261
Members
418,183
Latest member
skaufman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top