Error Message


Active Member
Nov 2, 2005
I want to refresh all my pivot tables but I'm getting an error. "Unable to get pivot table property of worksheet class" Also, I don't think this updates my source data... my pivot table source increases in size so how do I make the pivot table to pick up the new data also?

Dim Pt As PivotTable
Dim Wsht As Worksheet

Application.Cursor = xlWait
For Each Wsht In ThisWorkbook.Worksheets
For Each Pt In Wsht.PivotTables
Next Pt
Next Wsht
Application.Cursor = xlDefault

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Greg Truby

MrExcel MVP
Jun 19, 2002


pt.refreshtable<hr />

Use a dynamic named range as the source, you can type in the name to the wizard if you open the wizard with the table selected and click the back button a couple of time. Here's my boilerplate on DNR's if you are not familiar with them...

Dynamic Named Ranges

Dynamic named ranges can be used just like normal named ranges in formulas, or as sources for things like pivot tables and charts. However instead of a normal named range where the group of cells being referred to is static, dynamic ranges typically take advantage of the Offset() and CountA() [or Count()] functions to create a range that resizes to include new data appended to the bottom of the range.

To Create a Dynamic Range
  1. From the menu Insert | Name | Define...
  2. In the Names in Workbook textbox, type in the name for the Named Range - probably something like "Data" or "DataRange".
  3. In the Refers to textbox type in a formula like the following (note: you may find it helpful to hit the F2 key while in the textbox to put it into "edit mode"):<ul type=square>
  4. =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),9) to start at A1, include all non-blank rows and 9 columns. (Good example for pivot tables where you want to include headers.)
  5. =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1) to start at B2, include all numeric rows and 1 column. (Good example for a chart data series.)
  6. =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) to start at A2, include one less than the count of all non-blank rows and 1 column. (Good example for a chart category series where values are not numeric.)
[*]You're ready to reference the Named Dynamic Range like you would a normal Named Range.[/list]

Watch MrExcel Video

Forum statistics

Latest member