Refreshing Pivot Table data.

Posted by Nicole Griffin on August 15, 2001 8:56 AM

Can anyone help me with a macro that will refresh pivot table data? I set up the table for a colleague who isn't very computer savvy. The Pivot Table source will be expanding, and the traditional way of refreshing the table is a bit of a pain for her. I want a macro that will go to the data source and select all rows with data in them, then refresh the table. This seems like it would be a common request, but I haven't found any mention of it in my books or on the web. Or am I missing something?

Thanks

Nicole

Thanks

Posted by neo on August 15, 2001 9:58 AM

nicole,

you can add this code to a button or in your worksheet_change event:

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
pvtTable.RefreshTable

neo

Posted by Eric on August 15, 2001 10:04 AM

Lenze has a great answer to this!

Here's part of an e-mail Lenze sent to help me understand.

"1) If you use the Data>Form option to add records and you name your source
table "database" (other names might work), Excel will automatically expand
the range when you add a new record. Make sure the "Extend list formats and
formulas box is checked on the Options>Edit tab.

2) The way I prefer, however, is to use a dynamic named range. See
http://ozgrid.homestead.com/DynamicRanges.html for examples."

Hope that helps

Posted by Mark W. on August 15, 2001 3:14 PM

Such a button already exists [ ! ] on the
PivotTable toolbar.

Posted by neo on August 16, 2001 10:00 AM

of course it does. but nicole said the user thought refreshing through conventional means was too complicated for her (maybe she couldn't find the toolbar:-) ). hence the alternative...

neo