Step down a pivot table showing detail in new sheet

[03aviator]

I have a pivot table that lists people with exceptions and I need to create a worksheet for each person on the list listing the exception details. This list will change daily - sometimes two or three people with exceptions, other times with fifteen or more people with exceptions.

How do I set up a macro to start with the first person on the PT list and create their worksheet, then step down to the next person on the list and so forth... each time I try to do this, only the first person's worksheet is created, and depending on the code it will just keep creating multiple copies of this first person or it will stop and do just one copy of the first person's sheet.

I realtively new to VBA but anxious to learn more.

The name of the worksheet with the PT is "Create Agent Files" with the pivot table starting on line 6. In column "A" is the person's name, and in column "B" is the amount of exceptions - which when "double-clicked" will create a sheet with the needed detail.

Please help!

 

[GlennUK]

Can I ask a couple of questions?

Are all the exception details in the pivottable itself?

Can you have an extra field in the data as an exception flag, of either Name or blank ... calculated by formula so that Name only gets filled when a someone is an exception, otherwise blank?

 

[03aviator]

First - thank you so much for replying!

Yes, all the detail is in the Pivot Table and the table is set up to show only those people with exceptions. As an example:

Jim Faraday 2
Randy Tiff 11

The names are in column "A" and the exception count is in column "B". The listing starts on row "6" and it will vary in range based on the number of people with exceptions. One day it might cover A6:B12 and another day it might cover A6:B25.

When I double click on the number of exceptions, it creates the detail sheet listing the exceptions - each successive sheet, for each successive person, labled as sheet2, sheet3, etc.

One change my boss just threw at me: only create exception sheets for the people with greater than 15 exceptions.

Ideally, I would also like to save one column of data, such as loan numbers in column "D", from each sheet into new files labelled with the name of the person the exceptions belong to.

After creating and saving the individual files, I would also like to delete the detail sheets that were created in the main workbook, as well as close the newly created files for the people with exceptions.

Sorry if I'm asking too much; just trying to automate as much as possible to prevent keyboard typographical errors and also to speed up the total process.

Again, thanks for responding and any help you can provide is greatly appreciated.

 

[03aviator]

Just figured out how to limit my list of exceptions to those with values greater than 15 so that step is solved. This was accomplished through a filter on my pivot table.