delink all charts in worksheet
On Apr 15, 9:01 pm, "Jon Peltier"
wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutionshttp://PeltierTech.com
_______
"Gerry" wrote in message
oups.com...
I've used a macro written by Tushar Mehta todelinkcharts in my
worksheet.
I need todelinkall 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
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
|