I have the following macro that pulls out references to external files and
references the current sheet. I'm sure it's a simple fix to someone, but I
can't get it.

Sub SeriesFix()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim objCht As Object
Dim chtSeries As Series
Dim sName As String
Dim sReplace As String
Dim sTemp As String

'This sub will remove all references to files on other servers within the
'chart series

Set aWB = ActiveWorkbook

For Each aWS In aWB.Worksheets
Debug.Print aWS.Name
sName = "*" & aWS.Name & "*"
sReplace = "'" & aWS.Name & "'!"
For Each objCht In aWS.ChartObjects
With objCht.Chart
Debug.Print .Name
For Each chtSeries In .SeriesCollection
With chtSeries
If .Formula Like sName Then
sTemp = Replace(.Formula, _
sReplace, "")
Debug.Print sTemp, .Formula
chtSeries.Formula = sTemp <~~~~~doesn't like this

End If
End With
Next chtSeries
End With
Next objCht
Next aWS
End Sub

I'm guessing I need to change the X and Y parts of the series. Thanks for
any assistance.

Barb Reinhardt

Never mind. For some reason I thought it was looking at other workbook when
the worksheet name was defined as the workbook name I extracted the worksheet
from. No wonder it didn't work right.

Thanks anyway.

Barb

"Barb Reinhardt" wrote:

I have the following macro that pulls out references to external files and
references the current sheet. I'm sure it's a simple fix to someone, but I
can't get it.

Sub SeriesFix()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim objCht As Object
Dim chtSeries As Series
Dim sName As String
Dim sReplace As String
Dim sTemp As String

'This sub will remove all references to files on other servers within the
'chart series

Set aWB = ActiveWorkbook

For Each aWS In aWB.Worksheets
Debug.Print aWS.Name
sName = "*" & aWS.Name & "*"
sReplace = "'" & aWS.Name & "'!"
For Each objCht In aWS.ChartObjects
With objCht.Chart
Debug.Print .Name
For Each chtSeries In .SeriesCollection
With chtSeries
If .Formula Like sName Then
sTemp = Replace(.Formula, _
sReplace, "")
Debug.Print sTemp, .Formula
chtSeries.Formula = sTemp <~~~~~doesn't like this

End If
End With
Next chtSeries
End With
Next objCht
Next aWS
End Sub

I'm guessing I need to change the X and Y parts of the series. Thanks for
any assistance.

Barb Reinhardt