<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
- From the menu Insert | Name | Define...
- In the Names in Workbook textbox, type in the name for the Named Range - probably something like "Data" or "DataRange".
- 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>
- =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.)
- =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.)
- =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]