Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining Data (Excel 2003)

    I am working with a series of large Excel files (10,000+ records) where the format is identical. Other than cutting and pasting, is there some simple way to combine these into a single file?

    And if so, will this work with filtered files? That is, if I filter the initial files first then save them as such, can these be combined in filtered form? My initial files are too large to all be combined together (I've found with cut and paste) but if I first filter then down to about 1000 records each then size is no longer an issue.

    Any suggestions on this would be greatly appreciated. I tried Tools>Comapre and Merge Workbooks, but for some reason that option is not greyed out and thus not available.

  2. #2
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data (Excel 2003)

    Oops! make that "greyed out" as opposed to "not greyed out" at end.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data (Excel 2003)

    If you filter data (using AutoFilter or Advanced Filter), copy/paste will only transfer the filtered rows.

    If you have a large number of workbooks, and if the same "recipe" can be applied to each in order to filter and transfer the data, it might be worth while to write a macro to automate the process. If you would like help with that, we'd need to provide information on the structure of the workbooks and about the "recipe".

  4. #4
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data (Excel 2003)

    Thanks, good news that cut/paste will only transfer filtered rows

    Thanks for macro suggestion:

    I'd already done a macro that can be applied to each initial workbook that filters it as required, selects all its cells, and then copies this to the clipboard. So what I then do is paste that into the new workbook. I suppose I could write a macro in new workbook to do the pasting then move to the next blank row, then I'd be able to work quickly from original workbook(s) to the new one and back. Is this the optimum approach?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data (Excel 2003)

    What you could do is create a new folder and place all source workbooks (and no other ones) in this folder. You can then write code that loops through the .xls files in the folder, opens each in turn and copies the data, then closes the workbook and moves on.

    The 'skeleton' for the loop can be seen in <post:=597,258>post 597,258</post:>. Other posts with related code are <post:=572,285>post 572,285</post:> and <post:=551,595>post 551,595</post:>.

  6. #6
    Lounger
    Join Date
    Nov 2006
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data (Excel 2003)

    Thanks, I am not a "coder" but perhaps eventually someone here in IT can put this into practice here. Your assistance is much appreciated!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Data (Excel 2003)

    If you'd like more assistance, please provide more specific information - for example, how to filter the data to be copied.

Posting Permissions

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