How do I refresh a Pivot Table in Excel 2007 using VBA if the range of my data changes?
Posted on 2011-03-17
I'm running a query on an external set of data which then, saves the recordset data on a worksheet in an Excel ("source"). I then use the data on the "source" worksheet to update a pivot table in the same workbook ("target"). Because I am obtaining the data from a query, the number of rows in the "source" changes almost every time I run the query. The problem I am having is that when I try to refresh the pivot table, it is not collecting all of the data from the "source"; it is only using the old range to update the data, it is not expanding the range to include new data.
I am using the following code (snippet) to refresh the pivot table:
dim pvt as PivotTable
Can you tell me how I can get the pivot table to dynamically change the range of data it uses (using VBA)?