Find and Replace in Charts

**sagaramlani** · 11-08-2010, 12:55 PM

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, 06: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

**Andy Pope** · 11-09-2010, 04:47 AM

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

**wacky_diva** · 04-30-2014, 11:36 PM

Does this just update chart on the current worksheet?

**6StringJazzer** · 05-01-2014, 07:40 AM

For the code I provided, it depends on where you put the code. If you put the code in a Sheet module, as what I recommended to the OP, it will update the charts on that worksheet. If you put it in a Module module, it will update the charts in the active worksheet. But in either case, it only updates charts on one sheet.