Results 1 to 7 of 7

Transferring Data from Multiple Workbooks to a Master Workbook

Threaded View

  1. #1
    Registered User
    Join Date
    12-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    37

    Transferring Data from Multiple Workbooks to a Master Workbook

    Suppose I have multiple workbooks, each of which contain item IDs. One of these workbooks is a master list containing all IDs, and every other workbook contains a small piece of the master list. Additional information is added, in each small workbook, beside each item ID and the workbook is marked as complete. What I would like to do is have Excel check these smaller workbooks against the master list / master workbook. In particular, I would like Excel to check small workbook item IDs one by one and find the same ID in the master workbook. When a match is found I would like Excel to paste the new information gathered in the smaller workbooks to the master workbook.

    Unfortunately, there is a catch: Each smaller workbook has one ID reviewer, but the master workbook sometimes pairs these reviewers together to help ensure that all the necessary information has been included (two pairs of eyes are better than one). This means that the new information added by reviewers 1 and 2 is mashed together so that we have columns D1 E2 F1 G2 H1 I2 and so on, where 1 stands for person 1 and 2 for person 2. Note that D1 and E2 contain the same kind of information, just filled in by a different person. As such, for Excel to properly paste the information from the small workbooks it needs to not only check IDs, but once it has confirmed an ID match it needs to check whether the person's name in the small workbook (which is always 5 columns over including the 1st ID-bearing column) is 3 columns over or 4 columns over in the master workbook. If it is 3 columns over it needs to paste into D,F,H and so on, avoiding E,G, and I. The opposite is true if it is 4 columns over. It may be worth noting that it is possible for the 4th column over to be blank if only one person has been assigned to checking that particular item (the system is far from perfect).

    Would it be possible to design a macro that works by highlighting a range of IDs in the master workbook and automatically checking all small workbooks in a given file directory for IDs and names, and filling in the relevant information as described above? Please let me know if anything is unclear, as I realize this is a very specific question.

    Would something like this be a good start?

    With Application.FileSearch
            .NewSearch
            .LookIn = "C:\Documents and Settings\FalseDirectory\TargetFolder"
            .SearchSubFolders = False
            .Filename = "*.xlsx"
            .Execute
    
            For i = 1 To .FoundFiles.Count
            'Open each workbook
                Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
            'Perform the operation on the open workbook
    
                SOME OPERATION
    
            'Save and close the workbook
                wb.Save
                wb.Close
            Next i
        End With
    End Sub
    Attached Files Attached Files
    Last edited by Stopwatch; 12-20-2013 at 11:41 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Do Until Loop - Transferring data from several workbooks to a Master workbook
    By GalsenPAP in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-10-2013, 01:00 PM
  2. [SOLVED] Import data from multiple workbooks into master workbook
    By Jeepster325 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-07-2013, 06:44 PM
  3. [SOLVED] Move data from master workbook to multiple workbooks.
    By visha_1984 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-22-2013, 02:01 PM
  4. Copy Data from multiple workbooks to a master Workbook
    By zeeez in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-04-2012, 01:45 PM
  5. Replies: 5
    Last Post: 03-05-2012, 09:31 AM

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