Split Excel Workbook (multiple sheets) by tab names and merge into several work books

[mohara]

Hi, my first time posting as I've usually found what I need by googling and got it working but I am a complete novice.

What I'd like to do if anyone could help please is Split a workbook that has many tabs and then merge those tabs into several workbooks according to their groupings.

So I have a spreadsheet that has a datasheet per sales rep and then associated pivot tables with each of the datasheets, rep John has Datasheet John, Performance Pivot, Targets Pivot and so on and the same for every rep. These need to be shared with each rep into their folders on a box account. So I want to split the workbook by tabs and then bring the tabs together into multiple workbooks named after each rep. So that they can then be distributed to them via box. Is this possible? I have found how to split the sheet into individual tabs, how to move particular sheets to certain folders (via a list created in excel) but cannot find anything on this.

Any help would be appreciated.

Thanks a mil
Marian

[Crispy85]

Hi Mohara,

would this work for you?

Please Login or Register  to view this content.

i found it on the interwebz.

[mohara]

This looks like it is just splitting the file into a sheet for every tab? I need it to collate tabs back into a workbook based on the reps name. So in the initial sheet each rep has a datasheet and pivots from that datasheet, so maybe 4 sheets each by 20 reps, I don't want 80 new single sheets, I want 20 workbooks with 4 sheets in each.. Hope this is making sense. Thanks

[mohara]

Been searching since before I created this post and just can't find what I'm looking for, I don't think it's that difficult. Even when I record a macro it's just following my steps but I'm not sure how to make it more dynamic as I probably won't have all the same reps every month (as employees leave and are hired etc). Not sure where to go next..