Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    consolidating data from different workbooks into 1 (Excel 2000)

    Hi,

    I get hundreds of workbooks at month-end that I need to consolidate into a single data sheet. How could I automate this? Also, a lot of these billing data (in the form of workbooks) that I get have duplicate names but different values (typically, there could be people billed for some period in one project and then billed in some other). I need to automate the consolidation without any overwriting. Perhaps conditional formatting could be the solution (entries for the same person could be highlighted).
    Please help!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: consolidating data from different workbooks into 1 (Excel 2000)

    Some thoughts on a general solution, there is not enough detail to be too specific.

    Macro code to:
    1) Create a new workbook for the consolidation (or append to an existing one if desired) Set up header row, etc as desired
    2)Prompt for files to import [User selects from the list] Or if the directory is all for import just grab the entire list without promting
    3) open (one-by-one) the files (I would open as read-only) in the list and loop thru them. Wile open
    4) "grab" the data of interest and add it to the workbook
    5) close the file and do the same for the next
    6) after all files have been looped thru clean up the consolidated files: Sort, format, as desired.
    7) If there is a possibility of dupe entries, you could use adv filter to filter the entire list and only display the unique items, then copy this "filtered, unique list" to a new worksheet and then delete the original before you sort and format


    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: consolidating data from different workbooks into 1 (Excel 2000)

    See if This Message will get you started. If not, we will need more details on exactly what you want to do.
    Legare Coleman

Posting Permissions

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