Pivot table - printing specific data

[Guest]

I am trying to print a specific group of data from a pivot table. I learned
of 'show pages' but first may not have my fields properly assigned.

My initial data sheet starts with columns; regional manager, area manager,
sales rep, store # and then many columns of data; sales 2004, sales 2005....
The rows have the corresponding data and there are 50 sales reps each with
20-25 stores.

My pivot table has regional, area, sales rep and store # in the page fields.
All the other sales figures are in the data field.

If I want to print a page showing sales for each of the 25 individual stores
of a certain sales rep, I currently select the store #, print, change to the
next store #, print.....25 times.

Is there a way to arrange the fields so that I can use 'show pages' and then
print the 25 individual store pages for that rep when I print the workbook?

I notice that on the data sheet, I use autofilter, select the sales rep and
if I look at the store # list through the filter arrow, I only see those
stores associated with the rep.

On the pivot table, if I select the sales rep from the page field arrow,
when I click on the store # in the page field, I still see all 1000 stores.
Using 'show pages' would make 1000 pages when I want just the 25 stores for
that rep.

Any suggestions, please?

Doug

 

[Debra Dalgleish]

You can use Tom Ogilvy's multiple page field printing code shown here:

http://www.contextures.com/xlPivot09.html

To exclude page field combinations with no data, add a line to the
DrillPvt code, e.g.

If oTable.DataBodyRange.Cells.Count > 1 Then
If PrintFlag Then