Easy way to split out sheets from a pivot table?

[60614_Excel]

Hi, I am working on some sales data which I need grouped by Sales Rep Name. I have a pivot table with it grouped by Name with the data being number of accounts, but now I want to take each reps data and split it into their own worksheet eg: pivot table contains Smith- 4 , Thompson- 10, Ward-20 etc. and I want to make a sheet for each of these people with their respective account responsibilities.

I know you can double click on the data totals number in the pivot table to break it into a new sheet but I have 40 reps, is there an easy way to split all of these into different sheets without clicking on each total 40 times and then also change the name of the tab to reflect the rep name from the pivot table?
thanks!

 

[Mark W.]

See the Show Pages... command in the PivotTable toolbar menu to create separate worksheets for each Page field item.

 

[60614_Excel]

No matter where I click in the pivot table, the Show Pages button on the toolbar is grayed out however the pivot table wizard and field buttons both work, any ideas?

 

[Mark W.]

Does your PivotTable have a Page field, such as 'Sales Rep Name'?

 

[60614_Excel]

oh! no, i just had rep down the left as a row. i've never used the page function. BTW just saw another post on the ASAP utilities and they are incredibly helpful, i love this board!

 

[60614_Excel]

I moved the rep name to the page area and did show pages of all however this creates a page for each rep (which i want) but with the pivot table in each page (which i dont want).

I just want the actual data to appear (eg if you were in the pivot and double clicked on the number of accounts, it would show all of the data that the pivot table is based on). hope that makes sense..

 

[Mark W.]

Once you get a separate PivotTable for each Sales Rep then you can use drilldown to isolate the pertinent info for each. Another approach would be to use Advanced AutoFilters on the original data list to channel select data to separate worksheets.