+ Reply to Thread
Results 1 to 5 of 5

Search / Copy / Paste Between 2 Workbooks

  1. #1
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,369

    Search / Copy / Paste Between 2 Workbooks

    I have two very large databases. One comprises two full sheets and the other comprises 4 full sheets. The two sheet workbook utilizes columns A:X, while the four sheet workbook utilizes columns A:R.

    I am seeking assistance with performing the following ...

    Obtain a serial number in Workbook #1 / column B and match it with a serial number in Workbook #2 column B. When the match is complete, copy the data Workbook #2 in column E / same row as serial number and paste it into Workbook #1 column E.

    When performing the search in Workbook #2 it may be necessary for all 4 sheets to be searched in order to locate the matching serial number.

    So to state it some what more concisely ... Search Workbook #2 (possibly all four sheets) for serial number from Workbook #1. Match located ... copy data same row / column E and paste in Workbook #1 column E.

    NOTE :

    Now here is the challenge I am facing with my computer. It has one of latest cpu's and 32 gb of ram. BUT ... the amount of data I am faced with the above stated goal will most assuredly overwhelm my machine. I've already experienced Excel freezing up just trying to get all the data pasted into the sheets of both Workbooks.

    I am seeking a highly efficient vba macro that can take my machine by the hand and slowly walk it through all the required steps of processing the Workbooks.
    I am also very open to using something other than VBA if there is a guarantee it will work as desired.

    Suggestions ?

    Search image :

    Search n Copy Image.jpg

    Final product image :

    Final Result.jpg

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,685

    Re: Search / Copy / Paste Between 2 Workbooks

    I assume you tried both Find and Application.Match
    The inherent weakness of the liberal society: a too rosy view of humanity.

  3. #3
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,910

    Re: Search / Copy / Paste Between 2 Workbooks

    For starters...let's see the code you are making use of and perhaps a small little sample of these 2 files...
    I am guessing your actual has headers and no blank columns between...as per above images...
    Also...Are you looping the serial no's then looping all the worksheets then looping all the data in said worksheets to find a match...
    Or...As per jolivanes above...Not sure how big your dataset is...

    This might be a little more efficient...
    For testing...assumes no headers but at least no blank columns between...Col's A to E

    Please Login or Register  to view this content.
    Last edited by Sintek; 09-07-2024 at 05:08 AM.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,369

    Re: Search / Copy / Paste Between 2 Workbooks

    Sintek

    Thank you for your submission. I'll test it out here and see how things go. Presently I am having a lot of issues with my computer. The video card is breaking down and causing issues with processing the video signal to the monitor.
    I suspect there are other issues as well due to a myriad of smaller, periodic problems. How it is affecting Excel : workbooks that used to open and run without issue are now having to go through the 'recovery' process before the workbook
    will open. Excel doesn't want to open/run more than one file at a time.

    Ive ordered a new computer and waiting on its arrival.

    Thank you again and for your patience.


    jolivanes ... I havent given FIND or APPLICATION.MATCH a try. Ill look at them as well. Thank you.

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,685

    Re: Search / Copy / Paste Between 2 Workbooks

    Good luck with your new asset. Hope it resolves your problems.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy text from a cell paste to website search box and copy & paste results in same sheet
    By JohnnyBoyxxx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-26-2020, 06:06 PM
  2. Two Workbooks - Search for Values in Workbook 2, Copy and Paste in Workbook 1
    By CC268 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-27-2017, 11:17 AM
  3. Replies: 3
    Last Post: 12-19-2016, 12:20 PM
  4. Search workbooks and paste to ActiveSheet.Range
    By Bodaw in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-27-2015, 05:51 PM
  5. [SOLVED] Open Multiple Workbooks, Record names, Copy paste to Active Workbook, Close the Workbooks
    By vba_madness in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2013, 06:09 AM
  6. loop Search, copy paste,next search, copy paste ...
    By Macrodroid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2011, 03:19 AM
  7. [SOLVED] Can't Copy and Paste or Paste Special between Excel Workbooks
    By wllee in forum Excel General
    Replies: 6
    Last Post: 04-05-2005, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1