I see many posts about 'Cleaning up a Pivot', but I want to do the opposite.

I often have the problem that I need to add data to a pivot table that isn't
there. A good example of this is states. I have reports that need to have
all the states to match up to other reports.

I first tried to add the states to the bottom and let the pivot grab them
with a zero count, but this adds (blank) to every field do to the extra
lines. While they can be hidden it is a little sloppy.

What I figured out was that if I copy all the states into the pivot range,
refresh the pivot, then copy the original states back, refresh again, the
pivot will hold onto those values. I now have a macro that does this,
tricking the pivot into holding on to values not really in the data range.

This works but is an extra step I would like to do away with. Does anyone
know of a way to add values to a pivot directly through vba?

Thanks,
--bnpatten

I think you method is the safest way.

--
Regards,
Tom Ogilvy


"Bradford Patten" wrote in message
news:IbXeb.660973$Ho3.136695@sccrnsc03...
I see many posts about 'Cleaning up a Pivot', but I want to do the
opposite.

I often have the problem that I need to add data to a pivot table that
isn't
there. A good example of this is states. I have reports that need to have
all the states to match up to other reports.

I first tried to add the states to the bottom and let the pivot grab them
with a zero count, but this adds (blank) to every field do to the extra
lines. While they can be hidden it is a little sloppy.

What I figured out was that if I copy all the states into the pivot range,
refresh the pivot, then copy the original states back, refresh again, the
pivot will hold onto those values. I now have a macro that does this,
tricking the pivot into holding on to values not really in the data range.

This works but is an extra step I would like to do away with. Does anyone
know of a way to add values to a pivot directly through vba?

Thanks,
--bnpatten