Macro to create a new spreadsheet according to a sales rep data

Dear all,

Attached the sample excel file. I have a raw data spreadsheet which contains three columns (Customer, sales and sales rep). I want to draw the data according to different sales rep and copy to a new spreadsheet and the spreadsheet name is the sales rep name (e.g. David). Afterwards, I need to save each spreadsheet in a directory. The directory location should be C:\(Sales rep name)\(Sales rep name).xlsx (e.g. C:\David\David.xlsx). The sales rep folder assume is already created. Is it possible to do this with macro?

Your help would be greatly appreciated.

Best Regards
Jack

Yes! It works well! Thanks for your kindly help!

Hi,

After doing some testing, I found there is a new issue.

I added a new column 'DATE' between 'SALES' and 'SALESREP' and added one raw data row (Row 13) and have modified some codings to get the results.

However, I found that if it has same "CUSTOMER' under one 'SALESREP' in different row (e.g. row 12&13) in the table, the pivot table will sum up them but not separate them.

After clicking 'Make Sheets" button, the expected result I want is 'Charles (Expected)' worksheet, 'Charles' worksheet may not be the result I want.

Attached the adjusted file. Once again thanks for your kindly help.