Hi Kaper, i realised that if the data & the charts are on the sheet, the script works fine..however, when the charts & data are on different sheet, the script wont work..is there any solution for that?

i'm using the below script...


Sub Button1_Click()
Dim wd As New Word.Application
Dim doc As Word.Document
Dim myChart As ChartObject, i As Integer, seriesFormula As String
Dim rng As Range, hasNonempty As Boolean
Set doc = wd.Documents.Add
wd.Visible = True

For Each myChart In ActiveSheet.ChartObjects
  hasNonempty = False
  For i = 1 To myChart.Chart.SeriesCollection.Count
    seriesFormula = myChart.Chart.SeriesCollection(i).Formula
    seriesFormula = Mid(Left(seriesFormula, Len(seriesFormula) - 3), InStrRev(seriesFormula, "!") + 1)

    For Each rng In Range(seriesFormula)
      If Val(rng) <> 0 Then hasNonempty = True
    Next rng

  Next i
  If hasNonempty Then
    myChart.Copy
    wd.Selection.PasteSpecial _
    Link:=False, _
    DataType:=wdPasteEnhancedMetafile, _
    Placement:=wdInLine, _
    DisplayAsIcon:=False
  End If
Next myChart
End Sub