Error Message

Sharkie21

Active Member
Joined
Nov 2, 2005
Messages
319
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
ActiveSheet.PivotTables(Pt).RefreshTable
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
Joined
Jun 19, 2002
Messages
10,014
<s>ActiveSheet.PivotTables</s>(Pt).RefreshTable

i.e.

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

Threads
1,118,530
Messages
5,572,706
Members
412,481
Latest member
nhantam
Top