Copying worksheets & maintaining dynamic range graphs

[Mat_H]

Ok I've trawled the internet for too long now and still not found a solution so I figured I'd finally register and post a problem...

I have a database which I can query (osisoft PI for any process engineers) and I've set up a sheet to identify the start and end points of certain events that I'd like to examine in more detail. Using this table of start and end points I copy a template which queries my database for the relevant time period - thus dragging the relevant data into the copy of the template.

The template contains a chart which references named ranges (if that's the correct term, I used insert-> name -> define) so that it is dynamic. I need it to be dynamic as the size of the data series depends upon how long the event is.

The template sheet gets copied and a whole new batch of named ranges is created for the sheet (e.g. temp(2)!named_range) which is great. Unfortunately the chart on the copied sheet continues to reference the named ranges on the template, not the template copy so I go about trying to sort this out, the closest i've come is with this code:

With ActiveChart
    .ChartArea.Select
    .SeriesCollection(1).XValues = Range("'" & ActiveSheet.Name & "'!selected_date")
    .SeriesCollection(1).Values = Range("'" & ActiveSheet.Name & "'!selected_value")

This changes the series data for the chart to the named ranges but it leaves them the size they are at that instance i.e. B1:B2 & C1:C2 rather than selected_date and selected_value. This means that the chart is no longer dynamic.

Thanks in advance for any advice and don't hesitate to ask if you think I can clarify the problem in any way!