Merging multiple workbooks that contain identical tabs

I have multiple workbooks, all located in the same folder, and I want to merge them into a singular workbook. Each workbook contains 22 worksheets and each workbook have identical worksheet names. Each tab has a header that fills rows 1 through 5 after which the data starts. There is no naming convention for the workbooks as each come from seperate employees who name them what they will, but the template within thet workbooks remains the same.

I found the following code which is able to bring the workbooks together and recognize when two worksheets share the same name but instead of merging the two worksheets into one it appends a (2),(3), etc. when duplicate worksheet names are detected.

What I want to be able to do is select which workbooks I want to combine and then have the workbooks combine and have all the tabs that share names combine into a singular tabs.

If what I am asking is impossible or if I am being anyway unclear in what I am asking please let me know and I will attempt to clarify. I can upload a copy of the workbook I am trying to merge together if that helps.

here is the code i found previously:

Please Login or Register  to view this content.

Thanks in advance!

Hi Glurby, welcome to the forum.

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to EDIT that post above and put code tags around that code you used. (Like shown in my signature)

========
WORKBOOKS W/MULTIPLE SHEETS MERGED INTO SHEETS IN CONSOLIDATION WORKBOOK

Meanwhile, here's a macro for collecting data from all sheets in all files in a specific folder merging into matching sheets.

Workbooks to Sheets - Part 4

You will want to edit the fPath variable to your folder, or insert your own method of "selecting a folder".

You'll also want to edit this one line of code to copy from row6 on....

Please Login or Register  to view this content.

Slip that macro into a "master" workbook with all your sheet tabs laid out.

No problem. That macro suggested should work great for this.

the macro expects to be able to look UP column A to spot the last row of data for the LR variable. Is that not the case? Is Col A filled in for the data set all the way down? Sometime people make odd layout design choices like skipping column A.

Seeing a sample workbook, of course, always a good place to start. Your wb is probably too big. trim it down as a sample, or zip it up first.

I fixed the problem about the rows not copying correctly by altering the code slightly, by making it look up the C column not A.

But there is one problem still remaining, if one of the worksheets doesn't have any data the last row of the header is copied and put into the master, how can i avoid this?

I need to be able to because only certain employees fill out each worksheet, one may fill 20 of the possible 22 worksheets while another may only fill one of 22.

thanks so much.

Add an "IF" test on that copy line. Change this:

Please Login or Register  to view this content.

...to this:

Please Login or Register  to view this content.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

This macro is designed to "create" new sheets whenever one is needed by copying an already existing sheet called Report001, so for your workbook you would change that to the name of the sheet you want the macro to use.

Sounds like you're using the wrong macro from my site. You should be using PART 4 to bring in data from multiple worksheets into a master workbook where the sheet names match. Use the link in post #2, use the correct macro.

Start a new thread of your own, post your sample workbook(s) that include your edited versions of my macro in them.

You can send me an invitation to that thread once it is started. Include a link to it please.