melodramatic
Board Regular
- Joined
- Apr 28, 2003
- Messages
- 180
- Office Version
- 365
- Platform
- Windows
This one is way beyond my reach, but I know I want to do it - so hoping it's something you can teach me.
Working in Excel 2010, I have set up a macro that takes raw data files, puts them together into one continuous file, then sends that data to a chart file template. All that works fine, but I'm wanting to expand beyond that.
Depending on the type test that the data represents, I have 14 different chart file templates to which it may be posted - those files having anywhere from 3 to 22 charts, each on its own tab.
Right now, in order to keep the file sizes workable, I've hard-coded the X & Y values to =Data!$B$7:$B$20000 (on all files, Data is the data sheet, but the series letter varies). I went with 20000, as most of my tests fall within that range, and I'd like to keep the size of the file small. However, many times the file size is <5000, and I'd like to cut the series numbers to shrink the file size. On occasion, I have data ranges that go far beyond the 20000, and then I'm having to insert rows before pasting data to make the range in the charges larger.
I'd like to find out if there's a way to use VBA to set the ranges for all charts, all series, while keeping the correct column letter in the edit. I've already created a cell that does a count of the # of rows, so that would be the end of the rows range for me to use.
At this point, I'm planning on making a hidden sheet within each template, that lists each chart, along with the series for x/y values for that series, and let the macro scroll through that data and make the changes accordingly. However, is there a way that I can tell VBA to go to each chart, change Series 1 through 5, making their x/y values the same range as they are now, with the end row # changed?
Thanks for helping me out on the lazy path to doing this
Working in Excel 2010, I have set up a macro that takes raw data files, puts them together into one continuous file, then sends that data to a chart file template. All that works fine, but I'm wanting to expand beyond that.
Depending on the type test that the data represents, I have 14 different chart file templates to which it may be posted - those files having anywhere from 3 to 22 charts, each on its own tab.
Right now, in order to keep the file sizes workable, I've hard-coded the X & Y values to =Data!$B$7:$B$20000 (on all files, Data is the data sheet, but the series letter varies). I went with 20000, as most of my tests fall within that range, and I'd like to keep the size of the file small. However, many times the file size is <5000, and I'd like to cut the series numbers to shrink the file size. On occasion, I have data ranges that go far beyond the 20000, and then I'm having to insert rows before pasting data to make the range in the charges larger.
I'd like to find out if there's a way to use VBA to set the ranges for all charts, all series, while keeping the correct column letter in the edit. I've already created a cell that does a count of the # of rows, so that would be the end of the rows range for me to use.
At this point, I'm planning on making a hidden sheet within each template, that lists each chart, along with the series for x/y values for that series, and let the macro scroll through that data and make the changes accordingly. However, is there a way that I can tell VBA to go to each chart, change Series 1 through 5, making their x/y values the same range as they are now, with the end row # changed?
Thanks for helping me out on the lazy path to doing this
