Need Excel VBA to cycle through chart objects and change data source

Topic

New Reply

Hello – I have an Excel 2010 file that has several worksheets in it. Each worksheet has several bar, pie or column charts on them with the data source being the 2011DataPull worksheet. I need a macro to cycle through the sheets and change the data source of each chart or graph etc. to the 2012DataPull worksheet, the ranges will be the same – i just need the charts to point to a different worksheet.

for each worksheet in workbook
change the data range of each chart object from
=’2011DataPull’!…
to = ‘2012DataPull’!…
next worksheet

Any help is greatly appreciated!

Reply | Quote

Replies

hi jha,

You may not need a macro – a simple Find/Replace might do the job. if the objects are in-line, simply press Alt-F9, then use the before/after filenames as the Find/Replace expressions. Then Alt-F9, Ctrl-A, F9 should update the display.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I’ve tried Alt-F9 keys but nothing happens. When I use Replace and enter 2011DataPull for find and enter 2012DataPull in the replace box and I select workbook, it doesn’t search within the data source for each bar chart or pie chart in the workbook. It only changes my paste links not the data source of the chart objects.

Reply | Quote

Sorry jha – the Alt-F9 applies to Word. That said, a Find/Replace that changes 2011DataPull to 2012DataPull change the data links in all cells. Are you saying, though, that the charts are directly referencing cells in an external workbook, rather than referencing cells in the active workbook?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I have about 20 charts on 7 different sheets that reference the 2011DataPull worksheet within the same excel file. I need to change them all to reference a new worksheet (but the exact same ranges) named 2012DataPull. Thanks for the help – Joan

Reply | Quote

Hi Joan,

Try:

Code:

Sub Demo()
Dim wkSht As Worksheet, Obj As OLEObject
For Each wkSht In ThisWorkbook
  For Each Obj In wkSht.OLEObjects
    If Obj.OLEType = xlOLELink Then
      Obj.SourceName = Replace(SourceName, "2011DataPull", "2012DataPull")
    End If
  Next
Next
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote