How to maintain dynamic data range for Excel PivotTable

[char507]

Hi,

My basic problem is thus:

I have ~100 spreadsheets each containing pivot tables that get their data
from a source spreadsheet. Each month an unknown number of lines is dumped
into this source spreadsheet and the PivotTables are refreshed accordingly.
Right now, the PivotTable data sources are just looking at an arbitrary
number of rows in the source file to try and pull data. However, it looks
like in the next year, the amount of data will actually exceed the range
these tables are looking in. Therefore, while making some various other
automation changes, I would like to address this issue and maybe have the
pivotTables be updating their ranges dynamically.

So here is my current idea that I need a little help with (if there is a
better approach all-together, i'd love to hear about that as well.)

The basic logic being, when one of the pivot table sheets open, if the
current month is greater than the last month of data entered, the table
should go out to the source file and look for updated data (we dont know
exactly what day this file is updated). Is there a way to open that source
excel workbook and select all the data in a sheet behind the scenes? If so,
how would you do something like that?

Is there actually any harm to having the PivotTable looking at thousands of
blank rows for the data source?

Any thoughts / advice would be greatly appreciated!

- Charlie

 

[Michael]

If I was you I would consider using an Access Database, for the amount of
data you are working with is about to increase and you already a significant
number of spreadsheets. Access comes with a few database wizards that would
get you started and then you can start asking questions in the Access forum
just like you do here.

 

[char507]

I'm actually much more familiar with Access, SQL etc and I considered that
approach. However, this company is basically asking for an automated
"stop-gap" solution for about a year until the system can be completely
overhauled. Transferring the database to access would mean more work in the
~150 spreadsheet reports in order to connect and configure them for this new
data source.

 

[Michael]

The instruction to set up a range that will determine the Pivot table is
something like this:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet3!R1C3:R13C14").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

You can basically change the parameters to suit your needs, like the
Workbook name, and then insert a variable that looks at the last row of data,
instead of the R1C3:R13C14 value.
iLastRow=Range("A65536").end(xlup).row; this variable will get you the last
row with data.

 

[char507]

Alright so that seems like a good method but here is something else i hadn't
thought about. What if I used an external data connection? Each pivot table
is then based on that data connection to the source worksheet and refreshes
automatically (without code) upon opening?