Data Range - Change workbook

[Teddy]

Hi there,
I have searched through this forum cannot find a solution to what I need so
here goes (or perhaps I have missed it).

I have a workbook containing 11 worksheets - one for each country with the
first worksheet containing a set of standard charts which are reused for each
country. I am currently manually amending the data range and selecting the
worksheet and highlighting the data for each country (so have to do this 10
times). The data is in the same location on each country worksheet so really
the only amendment is the worksheet name.

How do you recommend that I speed up this process. I use a pivot chart for
another report and find brill for this type of thing as I just click the
country from the drop down box) and wonder if I could set up this type of
principal for my workbook.

Hope all this makes sense.

Rgds, Teddy

 

[Teddy]

In addition to below, I read about the chart formula on
http://peltiertech.com/Excel/Charts/ChgSrsFmla.html and when I tried manually
editing the chart formula it is very tricky and accidentally changes the
range rather than the worksheet name only. Also tried edit/replace which
does not work.

Thanks again. Teddy.

 

[Teddy]

I had another look at Mr Peltier's website and found my solution. If you
read this I want to say thank you so much for your brilliant solutions. You
have made my life so much easier.
Rgds, Ted

 

[Teddy]

Hi, took another look as Mr Peltier's blog and found my solution! Thank you
so much Mr Peltier for your wonderful solutions.
Rgds, Ted.

 

[Jon Peltier]

You're welcome.

- Jon