Relative worksheet reference in formula for chart data ranges?

Closed
Report
Posts
6
Registration date
Wednesday July 23, 2014
Status
Member
Last seen
July 29, 2014
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
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
Related:

5 replies

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
Posts
6
Registration date
Wednesday July 23, 2014
Status
Member
Last seen
July 29, 2014

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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
Posts
6
Registration date
Wednesday July 23, 2014
Status
Member
Last seen
July 29, 2014

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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Wait what... you have 60 graphs on a single sheet all with the same range!

So you want to copy 60 graphs from one sheet to the next 8 adjusting the range along the way.

Or am I misinterpreting you.

Never done that before, will get back to you if I can make that work.
Posts
6
Registration date
Wednesday July 23, 2014
Status
Member
Last seen
July 29, 2014

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



Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Collin,

I just thought of a simple solution.

Copy your original Graph sheet 8 times and then copy the data ranges to their correct spot.

This will keep all 60 graph at the same location and also their data ranges. All that is left, is to change the data in the data ranges.

PS. next time posting a picture, find "325px" at the end of the pictures code line and change it to "500px" to increase its size.

Best regards,
Trowa