Split Workbook Into Multiple Workbooks

[mlo356]

Hi All,

I am looking for a macro to split a workbook with one worksheet into multiple workbooks. Everyday, there is an excel report with hundreds of rows that multiple people have to work on. Because only one person can edit a workbook at any given time, I would like to split the workbook/worksheet and the data within into multiple workbooks. I would also like the header row on each worksheet within the newly created workbooks. For instance, see attached EXAMPLE SPLIT.xlsx

I would like a separate workbook for each tab (I used separate tabs within the same workbook to keep it simple). The macro would have an input box to collect the number of splits (workbooks to be created). For the attached example, I chose 3. There are 8 rows of data excluding the header row. 8 (rows of data) / 3 (split) = 3 (2.5 rounded) workbooks to be created. The first split contains the header row and data rows 2-4. The second split contains the header row and data rows 5-7. Lastly, the third split contains the header row and data rows 7-8.

Is this possible?

Many Thanks!!!

[werafa]

I think you are coming from the wrong end of the stick.

I've done a similar thing.
start with the workbooks for the individual users. (create these independently/manually)

Then create a reporting workbook that:
1 - locates the source data workbooks
2 - opens them silently (can do this even when they are still locked for editing)
3 - copies the data to the required columns in the reporting workbook
4 - closes the source workbooks
5 - produces any analyses and reports

2nd
should you really be using MS Access for your data collection