Find and Replace in Charts

**sagaramlani** · 11-08-2010, 11:55 AM

Hi,

I am wondering if anyone knows how to find and replace on Excel charts??

For example I have the following

'Sheet1' Worksheet on Workbook 1
'Results' Worksheet on Workbook 1

'Sheet 2' Worksheet on Workbook 2
'Results' Worksheet on Workbook 2

I have copied a chart from 'Sheet 1' on Workbook 1 to 'Sheet 2' on Workbook 2.

How can I replace the references on the Chart to 'Sheet 1' on Workbook 1 to 'Sheet 2' Workbook 2?

I have about 25 charts I am looking to do this for so I am looking on tips that would allow me to do this quickly...Possibly a macro which works.

Many thanks in advance.

Sagar

**6StringJazzer** · 11-08-2010, 05:47 PM

Do you mean update range references that appear in the SERIES formulas?

Install and run this code in your Workbook2, Worksheet2 code module. (This is suitable for a one-time update but is not highly robust against weird condtions.)

' Update all charts to use a different sheet reference
Public Sub Update()
   Dim ch As ChartObject
   Dim s As Series
 
   For Each ch In ChartObjects
      For Each s In ch.Chart.SeriesCollection
            s.Formula = Replace(s.Formula, "Sheet1", "Sheet2")
      Next s
   Next ch
 
End Sub