Create Chart in a new Workbook using VBA

[ChaosPup]

Hello all,

At the moment I run a macro to create a new chart in a new worksheet -

Sub create_chart_sheet()
Dim oChartSheet As Chart

Set oChartSheet = Charts.Add

oChartSheet.HasTitle = True
oChartSheet.ChartTitle.Text = "CARs By Product"

oChartSheet.HasLegend = False

oChartSheet.SetSourceData Sheets("COUNTS").Range("A3:B51")

End Sub

This works fine, what I really want though is for this macro to create a new workbook and add the new chart to it without making any changes to the source workbook. Is this possible?

Thanks!

 

[Domenic]

Try the following code....

Sub create_chart_sheet()

    'get the range for the source data
    Dim sourceDataRange As Range
    Set sourceDataRange = ThisWorkbook.Sheets("COUNTS").Range("A3:B51")
    
    'create a new workbook that contains a single blank worksheet
    Dim destinationWorkbook As Workbook
    Set destinationWorkbook = Workbooks.Add(xlWBATWorksheet)

    'create a new chart sheet and place it at the beginning of the destination workbook
    Dim oChartSheet As Chart
    Set oChartSheet = destinationWorkbook.Charts.Add(destinationWorkbook.Sheets(1))
    
    'set the properties for the chart
    With oChartSheet
        .HasTitle = True
        .ChartTitle.Text = "CARs By Product"
        .HasLegend = False
        .SetSourceData sourceDataRange
    End With
    
    'delete the unused worksheet in the destination workbook (optional)
    Application.DisplayAlerts = False
    destinationWorkbook.Sheets(2).Delete
    Application.DisplayAlerts = True

End Sub

Hope this helps!