delink all charts in worksheet

[Gerry]

I've used a macro written by Tushar Mehta to delink charts in my
worksheet.

I need to delink all worksheets and have tried adding some code to
activate the charts one at a time. It seemed to work fine one time,
but it's now giving me problems. Here is the code. Help me out if
you can.

TIA

Gerry

Sub DeLinkCharts()
''' Thanks to Tushar Mehta

Dim ChartNm As String
Dim ChtObj As ChartObject
Dim mySeries As Series
Dim sChtName As String

''' Make sure a chart is selected

For Each ChtObj In ActiveSheet.ChartObjects
ChartNm = ChtObj.Name
ActiveSheet.ChartObjects(ChartNm).Activate
ActiveChart.ChartArea.Select

On Error Resume Next
sChtName = ActiveChart.Name
If Err.Number <> 0 Then
MsgBox "This functionality is available only for charts " _
& "or chart objects"
Exit Sub
End If
If TypeName(Selection) = "ChartObject" Then
ActiveSheet.ChartObjects(ChartNm).Activate
End If
On Error GoTo 0

''' Loop through all series in active chart
For Each mySeries In ActiveChart.SeriesCollection
'''' Convert X and Y Values to arrays of values
mySeries.XValues = mySeries.XValues
mySeries.Values = mySeries.Values
mySeries.Name = mySeries.Name
Next mySeries
Next
Range("A1").Select
End Sub

 

[Jon Peltier]

Don't overcomplicate it, and don't bother activating each chart.

Sub DelinkCharts()
Dim sh As Object
Dim chtOb As ChartObject
Dim mySeries As Series

' loop through sheets
For Each sh In ActiveWorkbook.Sheets
' loop through chart objects on sheet
For Each chtOb In sh.ChartObjects
On Error Resume Next
' loop through series in active chart
For Each mySeries In ActiveChart.SeriesCollection
' Convert X and Y Values to arrays of values
mySeries.XValues = mySeries.XValues
mySeries.Values = mySeries.Values
mySeries.Name = mySeries.Name
Next mySeries
Next chtOb
Next sh
End Sub

- Jon

 

[Gerry]

Don't overcomplicate it, and don't bother activating each chart.

Sub DelinkCharts()
Dim sh As Object
Dim chtOb As ChartObject
Dim mySeries As Series

' loop through sheets
For Each sh In ActiveWorkbook.Sheets
' loop through chart objects on sheet
For Each chtOb In sh.ChartObjects
On Error Resume Next
' loop through series in active chart
For Each mySeries In ActiveChart.SeriesCollection
' Convert X and Y Values to arrays of values
mySeries.XValues = mySeries.XValues
mySeries.Values = mySeries.Values
mySeries.Name = mySeries.Name
Next mySeries
Next chtOb
Next sh
End Sub

- Jon

Hi Jon

Thanks for the response.

I tried your macro and nothing seems to work unless I have a chart
selected. Then it only delinks that chart only.

Gerry

 

[Tushar Mehta]

While anything's possible it is highly unlikely I wrote that code. The
style is very unlike mine, and the number of selects and activates is
something I haven't done for *at least* a decade.

The below is lightly tested. Run delinkChartsActiveSheet to delink all
charts in the active sheet and DelinkChartsActiveWorkbook to delink all
charts in all sheets in the active workbook.

Option Explicit

Private Sub delinkOneChart(aChart As Chart)
Dim aSeries As Series
'On Error Resume Next
For Each aSeries In aChart.SeriesCollection
With aSeries
.XValues = .XValues
.Values = .Values
.Name = .Name
'a bubble chart has one more element. BubbleSize?
End With
Next aSeries
End Sub
Private Sub delinkChartsOneSheet(aSheet As Object)
Dim ChartObj As ChartObject
If TypeOf aSheet Is Chart Then delinkOneChart aSheet
For Each ChartObj In aSheet.ChartObjects
delinkOneChart ChartObj.Chart
Next ChartObj
End Sub
Public Sub delinkChartsActiveSheet()
delinkChartsOneSheet ActiveSheet
End Sub
Public Sub DelinkChartsActiveWorkbook()
Dim aSheet As Object
For Each aSheet In ActiveWorkbook.Sheets
delinkChartsOneSheet aSheet
Next aSheet
End Sub

I've used a macro written by Tushar Mehta to delink charts in my
worksheet.

I need to delink all worksheets and have tried adding some code to
activate the charts one at a time. It seemed to work fine one time,
but it's now giving me problems. Here is the code. Help me out if
you can.

{snip}