asked on

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

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

regmigrant

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

Reg

andrewssd3

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.

Steven Vermoere

ASKER

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 ?

regmigrant

ActiveChart.Parent.copy
Reg

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steven Vermoere

ASKER

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