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

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 Solutions
http://PeltierTech.com
_______


"Gerry" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

On Apr 15, 9:01 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
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" <ger...@belshield.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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

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



In article <(E-Mail Removed)>,
(E-Mail Removed) says...
> 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}