Running a Find Macro Then Copy/Paste Corresponding Data

JML0328

Board Regular
Joined
Jul 24, 2010
Messages
62
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.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

JML0328

Board Regular
Joined
Jul 24, 2010
Messages
62

ADVERTISEMENT

HTML:
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.

HTML:
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
HTML:
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.
 

JML0328

Board Regular
Joined
Jul 24, 2010
Messages
62
Excel Workbook
ABCDEFGHIJKL
1Quote Entry Sheet
2Use this tab to enter your Request for QuoteRetrieve Quote #(INSERT QUOTE # HERE)
3
4RFQ NumberLineITEM/PART #DESCRIPTIONQTYU/MUNIT PRICE
5Job/Cost Code1
6Ship to Attn2
7Ship to Company Name3
8Ship to Street Address4
9Ship to City, State, and Zip5
10Ship to Phone#6
117
12Vendor Name8
13Vendor Street Address9
14Vendor City State Zip10
15Vendor Phone11
16Vendor Email/Website12
1713
18Shipping Carrier w/ Method14
19F.O.B.15
20Shipping TermsInternal Notes (will not show up in the RFQ)
21Shipping Cost
22Other Cost
23Extra 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
ABCDEFGHIJKL
1Saved Quotes
2Use this sheet to save quotes, to later convert to orders.
3
4
5Quote Date7/23/2010
6RFQ Number1001LineITEM/PART #DESCRIPTIONQTYU/MUNIT PRICE
7Job/Cost CodeT1Q5trgLaptop Battery4EA45.36
8Ship to AttnGilbert2T7opoCar Battery1EA27.64
9Ship to Company NameEast Canton Gas Co.3W967Battery Charger6EA14.47
10Ship to Street Address450 E Smith St4
11Ship to City, State, and ZipEast Canton, OH 446165
12Ship to Phone#330-458-12986
137
14Vendor NameHanson & Sons8
15Vendor Street Address125 East Chestnut St9
16Vendor City State ZipBoston, MA 0896610
17Vendor Phone540-258-474711
18Vendor Email/Websitewww.hansons.com12
1913
20Shipping Carrier w/ MethodUPS RED14
21F.O.B.SHIP COLLECT15
22Shipping TermsCollect# 675245Internal Notes (will not show up in the RFQ)
23Shipping Cost19.63
24Other Cost0
25Extra Comments
26
27
28Quote Date7/20/2010
29RFQ Number1LineITEM/PART #DESCRIPTIONQTYU/MUNIT PRICE
30Job/Cost CodeQ1Q5trgLaptop Battery2EA45.36
Saved Quotes


Excel Workbook
ABCDEFGHIJKL
1Quote Entry Sheet
2Use this tab to enter your Request for QuoteRetrieve Quote #(INSERT QUOTE # HERE)
3
4RFQ Number1001LineITEM/PART #DESCRIPTIONQTYU/MUNIT PRICE
5Job/Cost CodeT1Q5trgLaptop Battery4EA45.36
6Ship to AttnGilbert2T7opoCar Battery1EA27.64
7Ship to Company NameEast Canton Gas Co.3W967Battery Charger6EA14.47
8Ship to Street Address450 E Smith St4
9Ship to City, State, and ZipEast Canton, OH 446165
10Ship to Phone#330-458-12986
117
12Vendor NameHanson & Sons8
13Vendor Street Address125 East Chestnut St9
14Vendor City State ZipBoston, MA 0896610
15Vendor Phone540-258-474711
16Vendor Email/Websitewww.hansons.com12
1713
18Shipping Carrier w/ MethodUPS RED14
19F.O.B.SHIP COLLECT15
20Shipping TermsCollect# 675245Internal Notes (will not show up in the RFQ)
21Shipping Cost19.63
22Other Cost0
23Extra 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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,330
Messages
5,510,649
Members
408,806
Latest member
Hunlight

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top