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