Graph in multiple sheets but with the same selection from each sheet.

Hi All,
This is wonderful forum and I need a small help on my graph issue.

I have a workbook, which has 20 sheets, I am trying to draw a graph on each sheet ( for Ex. sheet1, a line graph , and data points will be use as Column B,C,D from sheet1)
So I have to draw the same graph on each sheet from their respective sheet Column B,C,D . So how to do that.

I can select all the sheets in 1 go and then can make selection of B,C,D column which will select these 3 columns from all the sheet but when I say Draw Line Graph , the menu will be disabled.

So to draw graph, I have to go to the each sheet and then I have to say insert line graph. To even simplify, I have added Line Graph as shortcut under "QuickAccess" menu. But still I have to click 20 times to draw the graph.

Please suggest is there any solution without using VBA.

There was one more problem using VBA, Using VBA, the graph alignment wasn't looking good compare to manual insert.

Thanks in advance for any inputs on this query.

I am using Office 2016.

Thanks,
Excel Curiosity

Thanks noboffinme for your reply.
Here with attaching a sample file for your reference.

All sheets will have same column header and I am plotting same type of graph. If I do with this VBA automation, graphs are coming with correct alignment.

Yes, this is going to be used many times. Can you please do one example and upload sheet here.. I will see your thoughts into actual implementation.

Yes, Those are the E/F/G columns. earlier, i was just referencing for EXAMPLE kind.

I appreciate your reply.

Hi Noboffinme,

Thanks for taking time and putting solution here.
But whatever steps you have mention 1 to 4 , I am doing those manually and it's not helping me to save my time.

As every time there will be a different excel file, may or may not have 20 sheets ( less or more) . This excel file is created automatically using Toad Automation designer module.

what I was looking for, to automate line charts of same type in one single click without navigating to 20 sheets or doing anything.

Best part is, the graph which i have to plot on each sheet for the respective data from each sheet, it needs to come from the same 3 columns.

Above suggested solution is the same thing I am doing.

I am doing one more step, If you select all sheet (using shift key , click first and last sheet and it will select all the sheets) and then Click the 3 columns (say E, F , G) then this selection will applies to all the sheets.


So that way, I am saving one step for each sheet of selection of columns. But when I want to drop line chart, that icon will be disabled. Generally MS Excel should allow that. (keeping is mind the selection is similar)

but looks like i need to write some fancy VBA code for to achieve this.

Thanks for your help.