Relative worksheet reference in formula for chart data ranges?Closed

Question

Hello, 

I am working on a project and was hoping for some help any and all excel wizzes if you have the time. Does anyone know how or if one can reference the current sheet in a formula for a chart data range? 

I am making a large number of graphs; each one references data on the same sheet, but there are 12 different sheets of data and graphs. They are all formatted the same, so I am wondering if there is a way to use a formula in the chart data range that references specific cells (A27:AI37, for example) on whatever sheet I paste the graphs to; that way I will only have to make them once. In other words, I want to make the graphs on the first sheet and then paste them to the other 11 and not have to re-select the data range for each of them.

Any advice would be greatly appreciated. Thanks a lot!

-Collin

TrowaD · Answer

Hi Collin,

The following code will change the data range:
Sub AdjustGraphRange()
ActiveChart.SetSourceData Source:=Range("A27:AI37")
End Sub

So after pasting your graph, run the code.

It's also possible to select a shortcut for the code in the macro select window (Alt+F8).

Best regards,
Trowa

Collin10592 · Answer

Trowa,

thanks a lot for your help! So this code will need to be run for each graph. correct? Is there a way to paste all 60 graphs and have the code change all of their respective data ranges to the same cells on the new sheet?

thanks,
Collin

TrowaD · Answer

Hi Collin,

For that I need a little bit more info.
Did you already create the sheets? If not how do you want to name them.

What I have in mind is that you manually copy the graph, then run the code.
The code will loop through all the sheets, pasting and adjusting the graph.
Are there any sheet names which need the excluded from the loop (i.e. don't need a graph pasted in it)? The sheet which has the original graph would be one of them.

Best regards,
Trowa

Collin10592 · Answer

Trowa,

the first two sheets, "Documentation" and "ABC SCM Top 10-linked" do not need graphs. the third sheet, "Top10 Comparison-Total Rev" already has all 60 graphs that I would like to copy to the next 8 sheets: "Top10 Comparison-Total Expense", "Top10 Comparison-per student"... and so on.

thanks,
Collin

Collin10592 · Answer

Trowa,

Here is a picture. Each sheet has 60 graphs, each with its own range from the data above. Each sheet will have the same cell ranges for the graphs but the data is different on each sheet. Let me know if this helps.

Thanks a lot,
Collin

Relative worksheet reference in formula for chart data ranges?

5 responses

Similar discussions

Subscribe To Our Newsletter!