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