Splitting sheets Data into Multiple workbooks

Hi All,

Hope you can help. I have been doing this manually for months now and i just wonder if there is Macro/VBA code that some can suggest that would make it a lot easier for me to create these sheets.

I have master spreadsheet (Test Data) which has 4 tabs. Once i update the data weekly, i then create 5 new excel spreadsheets by deleting the data accordingly. Each file is split by Regional Manager (Example attached). So i would have 5 spreadsheets named after the regional managers name. Is there a way to update the master and then click a macro that automatically splits the sheets by Regional manager(Column C) and then saves the file down to my desired destination path?

Any help will be much appreciated.

Thank you.

I think I would start by making five copies of the workbook and naming them after each of the Regional Managers … maybe add a formatted date. Then loop through the new workbooks and, within each one, loop through the sheets. Use AutoFilter to select, for example <>Henry, select the visible rows and delete them.

From a programming point of view, it might be easier to create a new workbook, say Henry, loop through the sheets to remove the entries that are not for Henry, and save and close the workbook. Then you don't need to keep track of five new open workbooks.

So
1 SaveCopyAs Henry
2 loop through sheets and delete non-Henry records
3 Save and Close
4 Repeat

As per Trevor suggestion...Sandy this assumes that all Regional managers are present in all sheets!

Please Login or Register  to view this content.

You're welcome. Thanks for the rep.

Glad I could assist...Tx for rep Sandy