Error Message

[Sharkie21]

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

 

[Greg Truby]

<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]