Professor Excel

Comments 19

  1. Daniel Wise

    Hi

    I want a routine to compare and merge some workbook into one or only merge some workbooks into one. I don’t want a only copy a sheet, no…. I want to MERGE copies of one workbook into one.

    I hope you can help me

    Thanks

  2. Sharyn

    Hi. I used method 4 to merge numerous excel files into one workbook.

    After each file it gives me a message that says “The name ‘NvsInstanceHook’ already exists. Click Yes to use that version of the name or click No to rename the version of ‘NvsInstanceHook’ you’re moving or copying”
    I click yes. Then it asks me if I want to save or not save the file. I click not save,

    How do I get the script to automate answering both those prompts so I don’t need to manually select?

    Also, how do I save this script so I can keep using it over and over again?

  3. Jacqui

    Hi

    I used the VBA method which worked like a charm except I had to click “save” after every file was processed. Is there any way to include that in the module?

    Thanks!

    Jacqui

    • Henrik Schiffner

      Hi Jacqui,
      Please replace this line:

      sourceWorkbook.Close

      by these lines:

      Application.DisplayAlerts = False
      sourceWorkbook.Close
      Application.DisplayAlerts = True

      This should avoid the error messages. But please make sure that your work is saved…

  4. Brent R.

    “Method 5: Automatically merge workbooks
    The third way is probably most convenient:”

    Is that a “Monty Python & the Holy Grail” reference? 😀

    • Henrik Schiffner

      Haven’t seen it actually. But I corrected the mistake… it’s the fifth way 😉

  5. Jonas Tiger

    Hi
    I tried method 4 (VBA) and it works fine for a simple merge.
    But what I want is to update the merge task withou duplicate sheets.
    Explaining:
    I have three large files updating in a daily basis (each one with one sheet). Then i need to merge all of them into one evaryday.
    Using your method 4 for second time, it duplicates sheets, instead of replacing the existing sheets.

    So, it would be nice if you provide the changes needed in your code to do the update and, if possible, to execute automatically every 24 hours.

    Thank You very much for your time
    JT

  6. hari

    i want to combine a specific sheet from the source files to a work book. Not all the work sheets in the source files.Suppose three product files , (product1 , product2 and product3) all the files has many sheets like sales,employees, expenses and so on. But i need only sales sheets from all the 3 product files into one work book (not in one work sheet)

    • Henrik Schiffner

      Hi Hari,
      I’m working on an advanced merge function for my add-in. But it’ll take some time.
      Best regards,
      Henrik

  7. vvs

    Hi
    when i used vba code it worked fine but i want all the data to merged in one single worksheet instead of seperate worksheets in one workbook.

  8. vvs

    Hi Henrik,
    Instead of running the code twice is there any chance that merging the data from different works books into one single workbook on one tym running the vba code .

  9. Jo

    What if you aren’t merging workbooks (.xlsx), but other data files saves as .csv, or .txt, or .dat .. can the VBA script handle those?

  10. sherazad

    Hi,

    Thanks very much for your code, I tried it, it works but I have this error message: Run-time error ‘1004’ Method ‘Copy’ of object ‘Worksheet’ failed, could you please help me on this.

    Best regards.

  11. vvs

    Hi Henrik, the code corked fine. now ,i want to delete the header rows in the merge file when it is executed and should display only final header row at the top but not all the header rows.

  12. Ali

    I have multiple workbooks with multiple worksheets (same columns for all workbooks, but different columns within each workbook ie. both book1 and book2 have sheet1 and sheet2, or more). Your code adds ALL sheets one after another, so I end up having 4 tabs in the output. Can you tweak it so sheet1 from book1 and book2 are merged in one new sheet, and sheet2 from book1 and book2 are merged in another sheet (2 sheets 1 workbook in the output)? Thanks!

  13. Renato

    HI Henrik,

    thank you very much for the VBA code.
    Could you please help me a bit related to that code?
    I should copy only the 2nd, 3rd and 4th sheets from each excel workbook into a separate one. How should I change the code to be able to avoid the remaining tabs?

    Thank you,
    Renato

  14. Joe

    I seriously love you for posting #4 (with the adjustment in your comment reply. Instead of requesting more customization I’ll tell you a story. I’ve been on a self-directed crash-course in VBA since stepping into an analyst role two weeks ago, tasked with (among other things) collecting and tracking responses for procedural non-compliance from a few hundred field units. Old method: field offices were sent a 32-column data export and attempted to copy/paste the rows which apply to their office into a separate “Response Form” (aka a Word doc) along with additional identifying info and investigation/action details. The analyst tracked responses by highlighting lines on a pivot table, which was challenging because few field units could successfully copy/paste 32 cells or provide all requested info or type a Subject line that distinguished one response from 100 others. My method: Turned the Word Doc into a 32-column worksheet, merged it with the data export, added a Double-Click event function so *anyone* can fully populate their response form directly from the data export tab; gave the form a 1-click “Submit” function which references identifying data (via their double-click event) to rename the worksheet which then duplicates and emails itself to me with a subject line that also references identifying data; found Outlook VBA that extracts all attachments from the folder which their responses (with those uniform and predictable subject lines) are filtered to, to an Import-folder on my desktop; and a tracker column on my master data export which matches identifying info from the response sheets as I import them into the tracking worksheet. The ONLY thing missing has been a convenient way to batch-import those attachments into the tracker without requiring an Add-In (miles of red tape to get one approved). I knew it could be done but was unable to find an answer for the past two weeks before stumbling onto this post. You’re a God-send at the end of a 13-hour day. Thank you.

Leave a comment