Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Vba code to merge differnt sheets from different workbook into different workbooks.

    Hi Experts
    I have found so many links online which merge sheets from different workbooks into one workbook. My requirement is different. I have 14 sheets/Workbooks saved in a common folder. Each sheet/Workbook contains 3 sheets/tabs each. I want to pull data from each tab/sheet from 14 sheets separately.
    Ex – Sheet1 from 14 sheets should be capture in one new sheet
    Sheet2 from 14 sheets should be capture in one new sheet and respectively for the Sheet3.
    Regards,
    JD

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jaggi

    It would be nice to know more about the data structure of the sheets being imported.
    For example, do the sheets have a header row??? Are they the same corresponding headers in each workbook??
    Are ALL the workbooks in a common folder going to be merged???
    Do the workbooks have the same structure???
    (For example, do they have the same sheet tab names in each workbook, in the same order??)

    What you ask for can be easily done.
    It would always be better if a typical sample file (or two) was provided - you can easily change edit any 'sensitive' data -we don't really care what it is.

    zeddy
    Last edited by zeddy; 2015-05-15 at 10:04.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post

    Hi Zeddy
    Please find my comments below.
    For example, do the sheets have a header row??? Are they the same corresponding headers in each workbook??
    JS – Yes the sheets do have Header Rows. Yes the headers will be same in each Workbook

    Are ALL the workbooks in a common folder going to be merged???
    JS – Yes, the workbooks are in a common folder

    Do the workbooks have the same structure???
    (For example, do they have the same sheet tab names in each workbook, in the same order??)
    JS – Nope the order should be same for all the workbooks, but the name is different. Can we pull sheet with its number same Sheet1, Sheet2 etc… instead of its name

    What you ask for can be easily done.
    It would always be better if a typical sample file (or two) was provided - you can easily change edit any 'sensitive' data -we don't really care what it is.
    JS – I have added 2 sample files with the thread.

    Regards,
    JD
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jaggi

    ..here's a sample Merge Tool that should get you started.

    zeddy
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Zeddy

    This is fantastic. Is it possible to not pull header from the sheets. Currently it is pulling that as well.

    Regards,
    JD

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Jaggi

    Yes, it is possible to skip the headers for importing.
    I deliberately left them in, so you could check that the correct sheet was appended, and to separate each file's data.

    option 1: You need to import them for the first file, then, after the first file, don't import the headers.
    option 2: Or, if you keep the headers in the import sheets (and adjust the [Clear] routine accordingly), then, you don't need to import ANY of the headers from the files.

    The simplest way would be probably be option 2.
    see attached

    zeddy
    Attached Files Attached Files

  7. The Following User Says Thank You to zeddy For This Useful Post:

    Jaggi (2015-05-18)

  8. #7
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Zeddy
    Thanks for the fantastic macro! It really fits into my requirement.
    Regards,
    JD

Tags for this Thread

Posting Permissions

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