Why is my excel Chart not going to a new workbook in VBA

I have a excel workbook that has values in them. I'm taking those values and creating a Chart with it using VBA. The chart is then saved to a worksheet on the same excel sheet as the values. I need the Chart to be moved to a different workbook. What am I doing wrong?

Code below:

Sub createColumnBarChart()
 
    
    'declare object variables to hold references to worksheet, source data cell range, created bar chart, and destination cell range

    Dim myWorksheet As Worksheet
    Dim mySourceData As Range
    Dim myChart As Chart
    Dim myChartDestination As Range
    
    'Variable declaration
    Dim sWorkbook As Workbook
    'Create New Workbook
    Set sWorkbook = Workbooks.Add
    
    
    sWorkbook.SaveAs Filename:="C:\Users\username\Desktop\myFolder\Test.xlsx"
 
    'identify worksheet containing source data and created bar chart
'    Set myWorksheet = ThisWorkbook.Worksheets("ChartData")
    Set myWorkbook = Workbooks("myFile.xlsx").Worksheets("ChartData")
 
    With myWorkbook
 
        'identify source data
        Set mySourceData = .Range("A1:AA26")
 
        'identify chart location
'        Set myChartDestination = .Range("A5:M24")
        Set myChartDestination = Workbooks("Test.xlsx").Worksheets("Sheet1").Range("A4:M24")
'
        'create bar chart
        Set myChart = .Shapes.AddChart(XlChartType:=xlColumnStacked, Left:=myChartDestination.Cells(1).Left, Top:=myChartDestination.Cells(1).Top, Width:=myChartDestination.Width, Height:=myChartDestination.Height).Chart
        Charts.Add
        
        
    End With
 
    'set source data for created bar chart
    myChart.SetSourceData Source:=mySourceData
End Sub

Solution

It's unclear if you want to copy the chart WITH the worksheet (and its data), or just copy "the chart" (which would just be a picture). If you copy the Chart object itself, it would link back to the original data in the source workbook (which I'm guessing is not what you want).

Your original code creates a chart (.Shapes.AddChart) and then creates a second chart (Charts.Add) which, because of how you created the new workbook is likely the currently active workbook. Plus, Chart.Add creates an empty chart - which is what you're seeing.

The example below creates your chart on the same sheet as your data, then shows two examples of how to copy or move the chart (and data).

Option Explicit

Sub TestMove()
    Dim myChart As Chart
    Set myChart = CreateChart
    
    Dim newWB As Workbook
    Set newWB = Application.Workbooks.Add
    
    Dim chartWS As Worksheet
    Set chartWS = myChart.Parent.Parent
    chartWS.Move Before:=newWB.Sheets(1)
    'newWB.SaveAs Filename:="C:\Users\username\Desktop\myFolder\Test.xlsx"
End Sub

Sub TestCopyPic()
    Dim myChart As Chart
    Set myChart = CreateChart
    
    Dim newWB As Workbook
    Set newWB = Application.Workbooks.Add
    
    myChart.CopyPicture
    newWB.Sheets(1).Paste
    'newWB.SaveAs Filename:="C:\Users\username\Desktop\myFolder\Test.xlsx"
End Sub

Function CreateChart() As Chart
    Dim dataWS As Worksheet
    Dim theData As Range
    Set dataWS = ThisWorkbook.Sheets("ChartData")
    Set theData = dataWS.Range("A1:AA26")
    
    Dim myChartDestination As Range
    Set myChartDestination = dataWS.Range("A4:M24")
    
    Dim theChart As Chart
    With myChartDestination
        Set theChart = dataWS.Shapes.AddChart2(Left:=.Cells(1).Left, _
                                               Top:=.Cells(1).Top, _
                                               Width:=.Width, _
                                               Height:=.Height).Chart
    End With
    
    With theChart
        .ChartType = xlColumnStacked
        .SetSourceData Source:=theData
    End With
        
    Set CreateChart = theChart
End Function