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

  • I am pretty new to VBA, but I am in the process of building multiple micro systems around an excel spreadsheet that is populated with Data.


    In the current micro system I am building I would like to type in a number in say cell F7 and type in cell I7 any text of my choosing.

    When I press a button I want the macro to copy cell I7 open another workbook with stored data, search for the value from cell F7 of other workbook in Collum A and paste the text from cell I7 into the same row of found cell, in to cell at collum Q.


    I hope I have explained myself ok, and look forward to any responses.

  • Hello and Welcome to the Forum :)


    Your question has been dealt with quite often in the Forum ...


    An important question, though, is to determine if you are working with opened or closed workbooks ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I would use a UserForm combined with .Find.


    Is the Workbook to search open or does it need opening?

    Hi Roy, it’s closed. But will be required to be opened then closed once the search has been completed and the cell populated on the same row of found cell in Collin Q after discovered search.

  • Hello and Welcome to the Forum :)


    Your question has been dealt with quite often in the Forum ...


    An important question, though, is to determine if you are working with opened or closed workbooks ...;)

    Thanks,


    the workbook containing the data to be searched will be closed, but the tool I wish to build to search for that data and copy info over from a set cell will be open when needed.

  • This is a quick example of what I am suggesting. I've adapted one of my userforms, if you think it will work for you I can add another TextBox to enter the text to co and code to copy it to the sheet

  • This is a quick example of what I am suggesting. I've adapted one of my userforms, if you think it will work for you I can add another TextBox to enter the text to co and code to copy it to the sheet

    Hi Roy,


    I'm sorry but that's not what I am looking for.


    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 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) 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 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.

  • I've amended the UserForm and I think it will do what you want


    Test it and let me know if it needs further adjusting.

  • Thanks,


    the workbook containing the data to be searched will be closed, but the tool I wish to build to search for that data and copy info over from a set cell will be open when needed.

    You are welcome :)


    If you feel like building a solid solution with ADO ...


    https://support.microsoft.com/…6a-91be-7fd4-4aadf1ff1afa


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I have 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 attachments are for demo use, and not real life data.

  • Try this. Let me know if it needs adjusting.


    I wasn't sure which sheet to search in the opened workbook, so the code loops through sheets. If there is a specific sheet or only one then the Loop can be removed.

  • Try this. Let me know if it needs adjusting.


    I wasn't sure which sheet to search in the opened workbook, so the code loops through sheets. If there is a specific sheet or only one then the Loop can be removed.

    sorry about that, it’s only to look at one sheet of data on the master spreadsheet.

  • Replace the code with this.


    Let me know if you need any changes

  • I forgot that you had uploaded the master WorkBoo, so I've just tested and it works OK.


    I've made a slight change though, just switched off screen updating to hide the other workbook opening.


    You will find that Column Q in the Master WorkBook is formatted as Currency and needs changing to a Number Format.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!