We help IT Professionals succeed at work.

How can I copy a chart to another sheet in same workbook with VBA ?

Steven Vermoere
on
Medium Priority
1,827 Views
Last Modified: 2012-05-12
Hello,

I want to copy a chart form several sheets to one sheet in VBA.  I do not want to paste it as a picture, but as a normal chart.

This is the code that I try, but it does not work. When I do a PasteSpecial, it copies as a picture, and I do not want this

CopyChart is called by CopyAllCharts

Thanks


Sub Copyallcharts()
    Call CopyChart("BE")
    Call CopyChart("NL")  
End Sub


Sub CopyChart(sCountry As String)

    Dim oWSChartImport                            As Worksheet
    Dim oWSChartExport                            As Worksheet
    Dim oSHPChart                                 As ChartObject
    Dim sRange                                    As String

    Select Case sCountry
        Case "BE"
            sRange = "A3"
        Case "NL"
            sRange = "A30"
    End Select

    'copy chart
    Set oSHPChart = ThisWorkbook.Sheets("CHART_" & sCountry).ChartObjects("CHART_" & sCountry & "_ALL")
    oSHPChart.chart.ChartArea.Copy

    'trying to paste it, but it does not work
    ThisWorkbook.Sheets("CHART_ALL").Range(sRange).Paste

End Sub

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
I think you need to select the parent object rather than the chart area:-
oSHPChart.ChartArea.Select
    ActiveChart.Parent.Cut
   
Reg
Top Expert 2011

Commented:
Another approach is to use the Location method:
Sub CopyChart(sCountry As String)

    Dim oWSChartImport                            As Worksheet
    Dim oWSChartExport                            As Worksheet
    Dim oSHPChart                                 As ChartObject
    Dim sRange                                    As String
    Dim rRange                                    As Range

    Select Case sCountry
        Case "BE"
            sRange = "A3"
        Case "NL"
            sRange = "A30"
    End Select

    'copy chart
    Set oSHPChart = ActiveWorkbook.Sheets("CHART_" & sCountry).ChartObjects("CHART_" & sCountry & "_ALL")
    
    Set rRange = ActiveWorkbook.Worksheets("CHART_ALL").Range(sRange)
    oSHPChart.top = rRange.top
    oSHPChart.Left = rRange.Left
    oSHPChart.Chart.Location Where:=xlLocationAsObject, Name:="CHART_ALL"

End Sub

Open in new window

You need to set the top and left before moving as the change of location seems to cause oSHPChart to lose its reference.

Author

Commented:
Hello,

This 2 last methods do a cut and paste. The chart in the source has disappeared. And it should stay. I just want a copy

The last method from andrewssd3 was on the good way.

Still suggestions ?

CERTIFIED EXPERT

Commented:
ActiveChart.Parent.copy    
Reg
Top Expert 2011
Commented:
Possibly the copy/paste is simpler, but having started with the location idea, you could duplicate then move:
Sub CopyChart(sCountry As String)

    Dim oWSChartImport                            As Worksheet
    Dim oWSChartExport                            As Worksheet
    Dim oSHPChart                                 As ChartObject
    Dim newChart                                  As Object
    Dim sRange                                    As String
    Dim rRange                                    As Range

    Select Case sCountry
        Case "BE"
            sRange = "A3"
        Case "NL"
            sRange = "A30"
    End Select

    'copy chart
    Set oSHPChart = ActiveWorkbook.Sheets("CHART_" & sCountry).ChartObjects("CHART_" & sCountry & "_ALL")
    
    Set newChart = oSHPChart.Duplicate
    Set oSHPChart = newChart.Parent
    
    Set rRange = ActiveWorkbook.Worksheets("CHART_ALL").Range(sRange)
    oSHPChart.Top = rRange.Top
    oSHPChart.Left = rRange.Left
    oSHPChart.Chart.Location Where:=xlLocationAsObject, Name:="CHART_ALL"

End Sub

Open in new window

Still quite efficient, and I always try to avoid using the clipboard if possible.

Author

Commented:
I changed 1 line:

    Set oSHPChart = ThisWorkbook.Sheets("CHART_" & sCountry).ChartObjects("CHART_" & sCountry & "_ALL")
   
    Set oCopyChart = oSHPChart.Duplicate
     
    Set rRange = ThisWorkbook.Worksheets("CHART_ALL").Range(sRange)
   
    With oCopyChart
        .Top = rRange.Top
        .Left = rRange.Left
        .chart.Location Where:=xlLocationAsObject, Name:="CHART_ALL"
    End With