Copying Worksheets from Multiple Workbooks to a Single Workbook, Separate Worksheets

ALCON,

I have a macro that will allow me to copy the first worksheet in a list of Excel workbooks, to a new workbook and place all of the worksheets on a single worksheet in the new workbook. That one is fine and I do not wish to break it.

However, I now have a different conundrum to overcome and that is the ability to copy all worksheets, in multiple workbooks, to a new workbook, but this time I want all worksheets copied to separate worksheets in the new workbook. Kind of like when you do it manually, and right click the tab, select 'Move or Copy', then choose which workbook you want them copied to. I want to automate that process down to pushing a button on a worksheet. I know there will be some looping involved, but trying to figure it out on my own without a VBA manual, makes it a little bit harder than usual.

So, has anyone done anything like this before? If so, I would accept any and all useful advice on this subject.

I'd like to thank everyone in advance for reading my post and for those who answer, my eternal gratitude.

DBH ends......

All the files I will be copying from are in a single folder, and there can be up to 5 sheets per workbook. However, not all workbooks will have 5 worksheets, some will have only 1, some 2, etc. I was going to execute the macro from a file that is one level up from the folder where the data is contained. I was going to prompt (with Inputbox) the user to tell the macro where the files are, and use that entered string to find the files. All files in the folder will be .xlsx files (Excel 2007 format)

Outstanding, I cannot thank you enough because this is EXACTLY what I was looking for, and you did it a lot easier than I would have done it (newbie VBA programmer that I am).

I wish there was something I could do to help you, and who knows, maybe someday in the future. Thanks again.

I am trying to take multiple Excel workbooks and merge them into one for summary purposes. What code is best for this? I have tried several and failed. Please help?

Hello sbastin, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

This is simply great solution. Can anyone guide me how to post my problem

Hello Abhi_1977, and welcome to the forum.

Go to the main page and, from the list of sub-forums you see there, choose the one that is most appropriate for your problem. Once in the sub-forum you'll see a blue button labelled "+ Post New Thread" at the top left. Click on that to open a new window where you can type your problem. Make sure to give your thread an acceptable title that complies with rule #1. If you haven't read the rules yet you should click on the Forum Rules button at the top of the page before trying to post.

Hello Cutter

Thanks a lot. I have successfully posted my problem.

Regards,

Abhijit.