B
Bradford Patten
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 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