writing a macro to move data from a pivot table to individual workbooks sorting by filter

I have a pivot table which is filtered based on the names of salespeople. Each week, they provide data on their weekly activities and the pivot table is updated. I want to give back to each individual agent a copy of their activities in a separate workbook.

I want to write a macro for each agent workbook that would go to the workbook with the pivot table and change the filter corresponding to the name of the agent for that particular individual agent spreadsheet. I would repeat this for each salesperson.

I tired to use the macro recorder. It worked when I could filter the names manually but the recorder did not record correctly ie which agent/salesperson the macro was suppose to sort on.

I have a feeling this is one of those that cannot be done using the macro recorder but has to be written in Visual Basic.

Any suggestions?