Adding data to the pivot table datasource

[Guest]

Hello
I have pivot tables that use data i import from Access. Each month the
import is larger and I have to go through each pivot table and change the
datarange. Is there a way to do this in VBA? There are 50 pivot tables on
this sheet.
Also I use MS Query to pull data from a smaller table in the same database
instead of exporting/importing. Would it be smarter/more efficient to use
this method in the case above? Can MS Query be controlled?
Thanks for your help!

 

[Tom Ogilvy]

Using ms query sounds like a good idea (I assume all your data is in an
external database).

See this article:

http://support.microsoft.com/?kbid=157488
XL97: Using Server Page Fields in PivotTables


otherwise, for data in your worksheets, you might use dynamic names

Insert =>Name => define

Name: Table1
RefersTo: =Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A$A),10)

then click add

now, in your pivot table, make the source the name Table1