Using Excel for Generating Unique Reports

Hi All
I have an excel question which i dont know where to begin so any help or guidance is very much appreciated.

I have an excel worksheet that list the Users Names, Email Address and the Dept they belong to. We are required to send out to each dept their list of users for their respective Head of Departments verification annually.

I have created a pivot table that we can select the relevant depts and the users list would be populated automatically.

My problem is how do i create a macro that can automatically cycle through all the depts in my list and export individual excel worksheet out as separate workbooks? Each workbook need to be renamed as their dept name.

Any ideas is appreciated.....

Based on your sample file - something like the below perhaps ?

Please Login or Register  to view this content.

the above, when run, will iterate each dept and create a new file with a copy of the PT output - each file will be created within the same directory as the "master" file (ie the file in which the code is placed) and each file will be named as per the dept it contains.