dotsofcolor
01-07-2012, 09:42 AM
Hello all,
I have tried some different variations of things but haven't found a solution to this one so thought I should ask for help. So I might need some help stepping into this one.
I included a spec sheet called Summary Calc and then a general mock up of what the data sheets will look like. The data sheets may vary a bit but they will pretty much look like this for now.
First step will be to create a new sheet and call it JAN11-IMP then create a connection to an outside data source, then bring in the data. Got that part okay. The data that will populate the JAN11-IMP will be from about a few cell rows to several thousand. The next month there would be a FEB11-IMP sheet and I would want the Column C on the summary sheet to populate with this new sheet data when available and so on throughout the year. So the January data would stay in place and a new sheet with a dynamic range would be added for February. Hope that part makes sense.
I would like to automate the next part. The Summary Calc sheet will then need to calculate each column (column B) as needed but since the range will be dynamic each time it is ran and the values will change how would I use vb code to do the functions provided in column B.
If possible this would need to be compatible with 2010 and 2003 versions.
I will be doing hundreds of these sheets and that is why I want to automate the calculation process. Any help would be greatly appreciated!
I have tried some different variations of things but haven't found a solution to this one so thought I should ask for help. So I might need some help stepping into this one.
I included a spec sheet called Summary Calc and then a general mock up of what the data sheets will look like. The data sheets may vary a bit but they will pretty much look like this for now.
First step will be to create a new sheet and call it JAN11-IMP then create a connection to an outside data source, then bring in the data. Got that part okay. The data that will populate the JAN11-IMP will be from about a few cell rows to several thousand. The next month there would be a FEB11-IMP sheet and I would want the Column C on the summary sheet to populate with this new sheet data when available and so on throughout the year. So the January data would stay in place and a new sheet with a dynamic range would be added for February. Hope that part makes sense.
I would like to automate the next part. The Summary Calc sheet will then need to calculate each column (column B) as needed but since the range will be dynamic each time it is ran and the values will change how would I use vb code to do the functions provided in column B.
If possible this would need to be compatible with 2010 and 2003 versions.
I will be doing hundreds of these sheets and that is why I want to automate the calculation process. Any help would be greatly appreciated!