Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to maintain dynamic data range for Excel PivotTable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to maintain dynamic data range for Excel PivotTable
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. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "char507" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to maintain dynamic data range for Excel PivotTable
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" wrote: 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. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "char507" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to maintain dynamic data range for Excel PivotTable
The instruction to set up a range that will determine the Pivot table is
something like this: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, 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. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "char507" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to maintain dynamic data range for Excel PivotTable
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to maintain a set range of cells when dragging a formula (e.gmonthlyannual data) | Excel Discussion (Misc queries) | |||
dynamic data range in pivot tables excel 2007 | Excel Discussion (Misc queries) | |||
Dynamic PivotTable Data Source | Excel Discussion (Misc queries) | |||
Dynamic Table Should Always Maintain Borders When Data Is Updated | Excel Discussion (Misc queries) | |||
PivotTable Dynamic Range | Excel Programming |