Regrouping worksheets from several workbooks into a single Workbook

Hi all,

I'm using Excel 2010. Is there a fast way (formula or VBA) to regroup worksheets (into a single workbook) from a same workbook that has been distributed to different users?

Here is a simplified version of the scenario in more detail: I have a Workbook called A and it contains worksheets named 1,2,3,4,....10. I have sent the workbook to 5 different users and each of them have worked on specific worksheets of the Workbook. There's no duplicate work done any user...by that I mean, worksheets 1 to 3 have been attributed to User 1, worksheets 4 to 5 to User 2, and so on.

Now that each user has returned the workbook with their worksheets filled, I need to regroup all the 10 worksheets into a single workbook.


Is there a fast way to do this rather than copy and paste each worksheet into a new book?


cross post: http://www.ozgrid.com/forum/showthread.php?t=178723

The macro can do the copy paste for you.

I have a question - If workbook A is being sent to User1, he will fill up only sheets 1 to 3. So will the other sheets exist in the file returned by him or will they be deleted? If they exist, will they be blank?

Ok, the reason i asked you is - when we consolidate all the sheets from all the files, there will be duplicate sheets created which wont be of any use. Hence, i thought that the macro can check if a particular cell like A1 or any other is not populated, then it does not need to consolidate that sheet.

I can help you. Which are the cells which need to be checked? Even one cell will do which you know will always contain data if its the user's sheet.

Actually, we do not need to mention the names of the sheets. The macro will loop through all the sheets in all the workbooks required and check if D7 is filled. If yes, copy that sheet to a new workbook, if not, skip it.

Are all the files stored in one folder?

You may not need to rename the files. How would you be receiving them? By email and then you would be saving them in one folder?

So when you save them, would they be having the filename as workbookname(1) then workbookname(2), etc?

No no, i do not need to set anything. However you save the files, as long as they are saved in one particular folder, the macro will pick up all the files in that folder and execute the macro.

I will provide you the code in sometime. The code will check for D7 be filled up in each sheet of each file. Only those sheets having that cell filled will be consolidated into a new workbook.

Try this code -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Thanks cytop.

zicitron,

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Hmm, i ran it at my end and i didnt get any errors.

Are both the workbooks - the New one created by the macro and the source file open?

Did you make any changes to the macro?

You do not need to save the file. Did you put the last "\" after the file path? The code will put that. You do not need to.

Works fine at my end using .xls files too. One question - where have you copied the code to? In a blank module?

Can you upload the file you are using after removing the sensitive data? I will try running the macro on your file.

When i ran the macro earlier, it had not given me the error. But when i ran it this time with your files i got it.

Change this line from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Am glad its solved.