Refreshing Drop Down Row Selections for Pivot Table
Posted by Mark Farr on October 25, 2001 12:56 PM
I am using the following macro on a button to refresh my pivot table. It does not refresh the Row Selections. How can I make sure that my row descriptions refresh also.
Sheets("Invoice query from AES2000").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Pivot Table").Select
Range("C14").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Thanks for any help.
Posted by Mark W. on October 25, 2001 1:02 PM
A Data Refresh (regardless, of how it is accomplished)
should update the PivotTable to include all available
row items. Is your PivotTable using data from your
worksheet? Is it possible that your extended your
database (list) and haven't updated your PivotTable's
data range?
Posted by Mark Farr on October 25, 2001 1:11 PM
New Items Added butt.
New items are listed and My dropdown selection includes new items but when I run the pivot table with different parameters Old drop down selections still show up in the drop down even though there is no data associated with them. The data itself is good it is just that the drop down selection of what row items I want to see is not changing dynamically with the new data. thanks
Posted by Mark W. on October 25, 2001 1:56 PM
Ahhh... Now I understand...
...you're deleting data from your data list and
can't understand why items that are no longer used
won't go away! You're not gonna like this, but
PivotTables have a very long (read this, infinite)
memory. The only way to get a PivotTable to forget
about "old" items is to re-create the PivotTable
using the revised data set. Now, I know what
you're thinking... this is incredibly deficient!
But... Believe it or not... There are some very
sound reasons (and uses) for this feature, and it
has to do with the "Show items with no data" check
box on the PivotTable Field dialog. Suppose that
you want a fixed-dimension PivotTable with column
items of "Yes" and "No". If your data set no
longer includes "No" records you'd just check
the "Show items with no data" check box to insure
that your PivotTable had fixed dimension. The
PivotTable must "remember" the prior existence
of "No" records for this to work properly!
