Hello Mynda and Paul.
When we last corresponded you helped me with creating charts with dynamic named ranges.
I have a spreadsheet with 18 identical sheets that present charts and tables corresponding to baseball games. 18 sheets because there can be up to 18 games on a single day.
I created the first sheet and explicitly edited the axis with the named ranges for each chart. There are 16 charts on each sheet. When I copied sheet 1 to a new sheet 2, the named ranges did not move to the new sheet. I spent the weekend explicitly updating named ranges (2 or 3 per chart) for 16 charts for 18 sheets! That's over 500 manual updates and I won't forget how to do that any time soon.
I kept searching for a way to copy a sheet or name the ranges in a way that would duplicate the ranges to a local scope on the new sheet, but nothing worked. So I just rolled up my sleeves to get it done.
I am sure you or others are howling at my lack of experience and the brut force I employed to get the results I wanted...but I sure hope there is another more efficient way in case I ever want to do something similar...or at least someone else can learn from my question here.
Any thoughts?
Thanks.
Barry
Hi Barry,
You'd have to write some VBA code to update the charts automatically, as there's no built in way to accomplish this.
However, I wonder why you need 18 sheets and don't just store your data in a single sheet (in a tabular layout as it should be). Then you could use PivotTables and Slicers to automatically choose different baseball games to display in the single set of charts.
Mynda
Hi Mynda.
Thanks for the reply.
I have 18 identical sheets so that I can create a detailed report (4 pages for each game) instantly. I have built a spreadsheet with many Pivot Tables and slicers, but for the report purpose I didn't want to click a slicer to select game, print and repeat 18 times. I simply drop in the day's games and all the 18 tabs of charts and tables are automatically created. I am printing a pdf report in under a minute!
Good to know my brute force was not wasted.
Barry
Ah, I can see why if you're needing to print to PDF.
