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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
<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]
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,023
Messages
5,834,996
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top