Copy All Sheets From Each Workbook in Folder to a Single Sheet in New Workbook

[DHartwig35805]

Greetings,

Yesterday I had asked about copying worksheets from many workbooks to a new workbook, and have each worksheet on a separate page. I got some outstanding help with the following code:

Please Login or Register  to view this content.

What I would like to do now is modify that existing code to have every worksheet from every workbook, copied to a new workbook, but put all of them on the same page, instead of separate pages. I know asked this once before, but it was for a different application, with a different style of worksheets, and I could not figure out how to make it work in this particular application.

Would it be as easy as changing the line of code that reads

Please Login or Register  to view this content.

And replace the letter I with the digit 1?

Or do I need to increment the rows as well so they don't all write over top of each other?

As you can see, having a little issue with this, but I do appreciate any assistance.

[patel45]

Please Login or Register  to view this content.

[HaHoBe]

Hi, patel45,

again:

Please Login or Register  to view this content.

is the last visible used row and your code overwrites this row each time a sheet is copied as no offset is used.

Ciao,
Holger

[patel45]

I'm very strong, thanks, the correct line is
LR = destWB.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 2

[DHartwig35805]

Thank you patel45 for your recommendation, and it worked, sort of. It copied data into the workbook I was executing the macro from and I wanted it to generate a new workbook, also It only copied the first worksheet in each of the workbooks and I need it to copy every worksheet from every workbook, to one page, in a new workbook.

Did I miss something? Here is the code I executed

Please Login or Register  to view this content.

If I missed something, I don't know where it is, so if anyone can help, please do.

---------- Post added at 02:04 PM ---------- Previous post was at 01:56 PM ----------

Ok, so I changed the "LR =" line to read

Please Login or Register  to view this content.

, but it still only got the first worksheet in each of the workbooks, when I need to get all the worksheets in each of the workbooks. So, what am I doing wrong? Also, it doesn't create a new workbook to put the copied data into, it writes it to the workbook that I am executing the macro from when I had hoped it would copy the data to a new workbook.

Thanks for all assistance so far, but still needs some tweaking....please.

[AB33]

I had the same issue as you. I have not seen any code which copies all sheets in workbooks in to single sheet. I think you can incorporate the following code in to your code. Currently, your code copies sheet one only, ie sheets(1). I think you need to change this line so your code loops in all sheets, not just the first one. But be carefull, if your sheets are so large, the code breaks. I do not know why.

Please Login or Register  to view this content.

[DHartwig35805]

Thanks AB33,

But I thought this would loop through all the worksheets in the open workbook:

Please Login or Register  to view this content.

Because in another macro that copies all worksheets in a folder to a new workbook with each worksheet on separate worksheets in the new workbook. But it isn't looping here properly, and for the life of me I cannot figure out why. I stepped through the code using 'debug.print' statements, but couldn't discover the logic error. I haven't programmed in a few years, so my skills are rusty, but this one has me stumped....the loop works in one macro, but not another...thank you Microsoft....

---------- Post added at 02:19 PM ---------- Previous post was at 02:15 PM ----------

Any way, here is the entire block of code, and I made a change to create a new workbook and put the copied data into it, but still can't get it to loop through all of the worksheets in all of the workbooks.

Please Login or Register  to view this content.

So, please, if anyone out there see's something I am missing, or typo's, anything, I'd appreciate it if you could point it out to me. Sometimes it is better when different eyes look at it because after the day I have had so far, everything looks fine, but I just know there is some small error. Thanks again.

[DHartwig35805]

Ok, maybe my skills aren't as rusty as I thought. I managed to fix this on my own, a miracle in itself, but here is what I did, I just move the "LR =" statement and put it into the For Each WS loop. Here is the code as it is after I changed it, and tested it to be sure it functioned properly.

Please Login or Register  to view this content.

And with that little change, it worked like it was supposed to, and I am as shocked as the rest. However, I would like to thank those who helped put me on the right path and maybe I can help you someday, should you ever have a finance problem, because I am a financial analyst, which is why my programming skills are rusty....thanks again to all.