Graphing with data from multiple sheets

[hegdep]

I've never done any graphing with macros earlier.

However, it would be nice to know how to perform operations like this one.
If I have say 'x' number of sheets and I want to trace and compare the graphical evolution of values on two particular columns on each sheet, what would be the simplest way to code it?

In the attached file, I have only three sheets. But, I am more looking for something that will help me easily loop through 100-150 sheets.
Assuming I insert a new sheet, could values from there be traced on to the new sheet too?

Also, is there any was of making it optional? Say, I want to view only graphs from particular sheets to compare them.
If the values were on the same sheet, I could have easily used the sort function to display a dynamic graph for me.

Thank you for your help in advance!

[dflak]

This application takes advantage of the fact that Excel Tables add a series every time you add a column. Keep the control panel sheet and Table sheet and make sure they are the first two sheets in the book. Add as many data sheets as you want.

Click the make data sheet and it creates the table which, in turn, creates the chart. You will get a series for every sheet in the workbook from sheet 3 on.

On the control panel, you get a listing of sheets and whether to show the sheet on the chart. By default, this is true. Click false and the table column is hidden which means the series on the chart is hidden. Click true and it is unhidden again.

The big issue is that there is no provision to add a sheet and just append it. You have to start from scratch when you add or delete sheets.

I abbreviated the data for the sake of testing.

[hegdep]

There seems to be some problem with this part of the code.

It highlights ".FullSeriesCollection" part of the code and pops up an error message which reads method or data member not found.

Please Login or Register  to view this content.

Also, could anybody recommend a good source to pick up charting via macros?

[AliGW]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[hegdep]

Attachment 527646

Please see the screenshot, I am very much in compliance of the rules. The coded part is very much in between

Please Login or Register  to view this content.

tags.
The website seems to have a bug!

My bad, I wasn't using the syntax correct. Can you delete this post and the previous one and please let the thread continue?
I am new on here. Still figuring my way around.

[dflak]

You have Excel 2010 - Microsoft changes the rules whenever they release a new version of Excel. I use FullSeriesCollection - you should use SeriesCollection (without the Full). I don't know why Microsoft thought it was necessary to make this particular change and break people's code.

Just be aware that when you upgrade, your code will no longer work since SeriesCollection has been "depreciated" in Microsoft-speak.

[hegdep]

Thanks a ton for your help!
Even though a little complicated, it works perfectly fine! It was a great exercise for a noob like me, to split the code apart and learn.
I just have one question, though. How do you get your chart to hide the column when the Boolean reads false and not display it on the chart?

Basically, when a detail is false on the control panel, that column should hide on the "Table" sheet. How do I make this happen?

Or, if it is a part of ModClearTable3, could you please explain it to me in greater detail?

I really appreciate your help on this. I am sorry for getting back to you this late. I was tied up with a bunch of other things.

[dflak]

I probably could hide the rows with False, but then how would you access them to turn them back on?

It would probably be easier to use the table as a source for a pivot table and filter on TRUE in the show column.

[hegdep]

Thanks a ton!One final question, though. How do you create a new chart that takes in data from a dynamically changing table?