Hi, I found this in another forum and it helped me

To do this, assign a "range name" to the master sheet and save the file. In a new workbook, click Data-(GetExternalData)FromOtherSources-FromMicrosoftQuery, then in the window that pops up select ExcelFiles, then select your master workbook, then in the Microsoft query box that pops up select the range name you assigned, then select all fields, then add filter for one of your subgroups, the click File-ReturnDatatoExcel. Now repeat for the other 8 subgroups each in their own worksheet. You can set the Data-Connection-Properties to refresh automatically upon opening the workbook.