Excel Charts - Copying and pasting to new destination cells

Hi,

I have a need to create a basic excel bar chart showing actuals and budgets side by side, the issue I have is I need to do this hundreds of times (With the data changing dependent on location selected).

I was hoping I could copy and paste the charts, but whenever I copy a chart it always references the original data, it doesn't move to the new data set I've just copied to.

I've tried removing the $anchors min the chart range but it doesn't except that.

Hoping that this maybe a simple issue and potentially an option/setting I can change

Any help will be gratefully appreciated as it's gonna take days to copy and redirect all the charts I need to create.

Thanks

Originally Posted by Jimbo79

I have a need to create a basic excel bar chart showing actuals and budgets side by side, the issue I have is I need to do this hundreds of times (With the data changing dependent on location selected).

Without actually seeing what you're data looks like and what you're doing with the chart, have you considered using a pivot chart?

I've added a sample chart, essentially I want to copy the London Chart down hundreds of times for other locations (the data being linked to source data in another tab with sumifs), but if I do the chart is fixed to the original set of numbers that I copied it from, it doesn't relatively move to the copied cells.

How about something like this? Again, I have no idea what your source data looks like, so I can't a whole lot of help when it comes to design. However, I would look to tackle it like this.

Originally Posted by Jimbo79

A dynamic chart isn't suitable as I have to produce several hundred charts (Every UK postal code to give a sense of scale), all of which will be Macro'd into independent report packages for consumption.

Are you good with using VBA?

Several hundred charts seems like a task for VBA. However, the question still exists, how does the data get from one range of reference cells to the next?

VBA would need to know where those references are and then maybe we could create those charts. I might be able to do this in VBA, but first, how about creating a sample document with the data for about five charts worth and then how do you intend to get the data from range to range.

So, for example, if you are looking at every UK postal code, there must be a way to extract that information one postal code at a time. You then build that one chart off of the first extract. Then you repeat that process postal code after postal code.

This can be done pretty easily with VBA. Just need a sample of the raw data to test with.

Originally Posted by Jimbo79

I can use VBA (prefer not to on multiuser spreadsheets), but I don't think the source data is relevant (at least in my mind).

Relevant is subjective to who is helping you find a solution here. I don't know of a way to copy and paste the chart and data and for it all to follow. It does seems VBA is the best solution.

Since I don't have a manually solution, I'll post this thread to our "help" area to see if somebody else has something to provide.

Hang on