Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called

Question

This is the code in an Excel user form. I have two subroutines. Number 1 CreatePivotTable & Nummber 2 CreatePivotCharts. If I run them consecutively when comandPivot is clicked, the pivot tables are created properly but I get an error in CreatePivotCharts in the line that is comented " 'ERROR OCCURS ON NEXT LINE". The error is "SetSourceData of Object _chart failed." However if the pivot tables exist and I run CreatePivotCharts by itself, there is no error and the 4 charts are created. I'm not sure why when it runs u=independently there is no error but when it runs after CreatePivotTable there is an error. Any help is appreciated. TIA

Option Explicit
Dim wbData As Workbook
Dim wsSetup As Worksheet
Dim wsLocations As Worksheet
Dim wsConsolidated As Worksheet
Dim wsDashboard As Worksheet
Dim selectedLocation As String
Dim message As String
Dim lastRowInConsolidatedWorksheet  As Long

Private Sub cmdCharts_Click()

    Call CreatePivotCharts
End Sub

Private Sub cmdControlPanel_Click()
    Unload Me
    frmControlPanel.Show
End Sub
Sub CreatePivotCharts()
    Dim pt As pivotTable
    Dim chart As ChartObject
    Dim filterField As PivotField
    Dim filterValue As String

    Dim chartTop, chartLeft As Integer
    Dim verticalSpacer, horizontalSpacer As Integer
    Const CHARTS_TOP = 350
    Const CHARTS_LEFT = 50
    Const CHART_WIDTH = 400
    Const CHART_HEIGHT = 400
    
  
    verticalSpacer = 50
    horizontalSpacer = 100
    
    wsDashboard.Activate
    ' Set the pivot table object
    Set pt = ActiveSheet.PivotTables("ptUsageData") ' Replace "YourPivotTable" with the name of your pivot table
    
    ' Set the pivot field object
    Set filterField = pt.PivotFields("Location") ' Replace "YourField" with the name of the field you want to filter on
    
    ' Retrieve the filter value from the pivot table
    If filterField.CurrentPage <> "(All)" Then
        filterValue = filterField.CurrentPage.Name ' Get the name of the selected item
    Else
        filterValue = "(All)"
    End If
    
    '***************************************
    '   Usage Column Chart (I)
    '***************************************
    ' Create a new chart and assign a name to it
    Set chart = ActiveSheet.ChartObjects.Add(Left:=CHARTS_LEFT, Top:=CHARTS_TOP, Width:=CHART_WIDTH, Height:=CHART_HEIGHT)
    chart.Name = "chtUsageColumn"
    
    ' Set the source data for the chart
    chart.chart.SetSourceData Source:=pt.TableRange1
    
    ' Customize the chart properties
    chart.chart.ShowAxisFieldButtons = False
    
    ' Apply the filter to the chart
    chart.chart.PivotLayout.pivotTable.PivotFields("Location").CurrentPage = filterValue ' Replace "YourField" with the name of the field you want to filter on
    
       ' Enable automatic update for the chart
    chart.chart.SetElement msoElementChartTitleAboveChart ' Change the chart element to trigger automatic update
    
     ' Hide the filter buttons on the chart
    chart.chart.ShowAllFieldButtons = False
    
    
     ' Set the chart title
     Dim titleText As String
     titleText = "Usage Data"
     chart.chart.HasTitle = True
     chart.chart.ChartTitle.Text = titleText
    
    ' Refresh the chart to apply the filter
    chart.chart.Refresh
    Set chart = Nothing
    '***************************************
    '   Usage Line Chart (II)
    '***************************************

    

    ' Set the pivot field object
    Set filterField = pt.PivotFields("Location")
    
    ' Retrieve the filter value from the pivot table
    If filterField.CurrentPage <> "(All)" Then
        filterValue = filterField.CurrentPage.Name ' Get the name of the selected item
    Else
        filterValue = "(All)"
    End If
    
    ' Create a new chart and assign a name to it
    chartTop = CHARTS_TOP + CHART_HEIGHT + verticalSpacer
  
    Set chart = ActiveSheet.ChartObjects.Add(Left:=CHARTS_LEFT, Top:=chartTop, Width:=CHART_WIDTH, Height:=CHART_HEIGHT)

    chart.Name = "chtUsageLine"
    ActiveSheet.ChartObjects("chtUsageLine").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.ChartArea.Select
    ActiveChart.ChartType = xlLine
    
    ' Set the source data for the chart
    chart.chart.SetSourceData Source:=pt.TableRange1
    
    ' Customize the chart properties
    chart.chart.ShowAxisFieldButtons = False
    
    ' Apply the filter to the chart
    chart.chart.PivotLayout.pivotTable.PivotFields("Location").CurrentPage = filterValue
    
       ' Enable automatic update for the chart
    chart.chart.SetElement msoElementChartTitleAboveChart ' Change the chart element to trigger automatic update
    
     ' Hide the filter buttons on the chart
    chart.chart.ShowAllFieldButtons = False
    
    
     ' Set the chart title

     titleText = "Usage Data (YOY)"
     chart.chart.HasTitle = True
     chart.chart.ChartTitle.Text = titleText
    
    ' Refresh the chart to apply the filter
    chart.chart.Refresh
    Set pt = Nothing
    Set chart = Nothing
    '***************************************
    '   Demand Column Chart (III)
    '***************************************

     ' Set the pivot table object
    Set pt = ActiveSheet.PivotTables("ptDemandData")
    
    ' Set the pivot field object
    Set filterField = pt.PivotFields("Location")
    
    ' Retrieve the filter value from the pivot table
    If filterField.CurrentPage <> "(All)" Then
        filterValue = filterField.CurrentPage.Name ' Get the name of the selected item
    Else
        filterValue = "(All)"
    End If
    
    ' Create a new chart and assign a name to it
    chartLeft = CHARTS_LEFT + CHART_WIDTH + horizontalSpacer

    Set chart = ActiveSheet.ChartObjects.Add(Left:=chartLeft, Top:=CHARTS_TOP, Width:=CHART_WIDTH, Height:=CHART_HEIGHT)
    chart.Name = "chtDemandColumn"
    
    ' Set the source data for the chart
    'ERROR OCCURS ON NEXT LINE
    chart.chart.SetSourceData Source:=pt.TableRange1
    
    ' Customize the chart properties
    chart.chart.ShowAxisFieldButtons = False
    
    ' Apply the filter to the chart
    chart.chart.PivotLayout.pivotTable.PivotFields("Location").CurrentPage = filterValue ' Replace "YourField" with the name of the field you want to filter on
    
       ' Enable automatic update for the chart
    chart.chart.SetElement msoElementChartTitleAboveChart ' Change the chart element to trigger automatic update
    
     ' Hide the filter buttons on the chart
    chart.chart.ShowAllFieldButtons = False
    
    
     ' Set the chart title
     
     titleText = "Demand Data"
     chart.chart.HasTitle = True
     chart.chart.ChartTitle.Text = titleText
    
    ' Refresh the chart to apply the filter
    chart.chart.Refresh
    Set chart = Nothing
   '***************************************
    '   Demand Line Chart (IV)
    '***************************************
    

    ' Set the pivot field object
    Set filterField = pt.PivotFields("Location")
    
    ' Retrieve the filter value from the pivot table
    If filterField.CurrentPage <> "(All)" Then
        filterValue = filterField.CurrentPage.Name ' Get the name of the selected item
    Else
        filterValue = "(All)"
    End If
    
    ' Create a new chart and assign a name to it
    chartLeft = CHARTS_LEFT + CHART_WIDTH + horizontalSpacer
    chartTop = CHARTS_TOP + CHART_HEIGHT + verticalSpacer
    Set chart = ActiveSheet.ChartObjects.Add(Left:=chartLeft, Top:=chartTop, Width:=CHART_WIDTH, Height:=CHART_HEIGHT)

    chart.Name = "chtDemandLine"
    ActiveSheet.ChartObjects("chtDemandLine").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.ChartArea.Select
    ActiveChart.ChartType = xlLine
    
    ' Set the source data for the chart
    chart.chart.SetSourceData Source:=pt.TableRange1
    
    ' Customize the chart properties
    chart.chart.ShowAxisFieldButtons = False
    
    ' Apply the filter to the chart
    chart.chart.PivotLayout.pivotTable.PivotFields("Location").CurrentPage = filterValue ' Replace "YourField" with the name of the field you want to filter on
    
       ' Enable automatic update for the chart
    chart.chart.SetElement msoElementChartTitleAboveChart ' Change the chart element to trigger automatic update
    
     ' Hide the filter buttons on the chart
    chart.chart.ShowAllFieldButtons = False
    
    
     ' Set the chart title

     titleText = "Demand Data (YOY)"
     chart.chart.HasTitle = True
     chart.chart.ChartTitle.Text = titleText
    
    ' Refresh the chart to apply the filter
    chart.chart.Refresh
    Set pt = Nothing
    Set chart = Nothing
    
    
End Sub





Private Sub cmdPivot_Click()

    Call CreatePivotTable
    Call CreatePivotCharts
End Sub

Private Sub UserForm_Initialize()
    Me.Height = 700
    Me.Width = 800
    SetUp
    If wsConsolidated.Cells(2, 1) = "" Then
        cmdPivot.Enabled = False
        'cmdChart.Enabled = False
        message = "There is no data in the Consolidated worksheet." & _
            vbCrLf & "Verify that data entry has been completed."
        frmMessage.Show
        Else
        cmdPivot.Enabled = True
    End If
  
End Sub
Private Sub SetUp()

    Set wbData = Workbooks("Data.xlsx")
    Set wsLocations = wbData.Worksheets("Locations")
    Set wsConsolidated = wbData.Worksheets("Consolidated")
    Set wsDashboard = wbData.Worksheets("Dashboard")
    Call ConsolidateLocationData(wsLocations, wsConsolidated)
End Sub
Private Sub ConsolidateLocationData(wsLocations, wsConsolidatedLocations)

    'Loop through locations
    Dim lastRow As Long
    Dim startRow As Long
    Dim lastRowInTargetWorksheet As Long
    Dim consolidatedRange As Range
    Dim cell As Range
    Dim dataRange As Range
    Dim dataRangeNoHeader  As Range
    Dim targetWorksheet As Worksheet
    Dim targetWorksheetName As String
    Dim arr As Variant
    Dim numRows As Long
    Dim numColumns As Long
    Dim i As Integer
    
    wsConsolidatedLocations.Cells.Clear
    Call WriteHeaderNew(wsConsolidatedLocations)
    
    startRow = 2
    With wsLocations
    
        lastRow = .Cells(.Rows.Count, dcLocationDisplayName).End(xlUp).Row
        
        For Each cell In .Range(.Cells(startRow, dcLocationDisplayName), .Cells(lastRow, dcLocationDisplayName))
            targetWorksheetName = cell.value
            Set targetWorksheet = wbData.Worksheets(targetWorksheetName)
            
            wbData.Worksheets(targetWorksheetName).Activate
            lastRowInTargetWorksheet = targetWorksheet.Cells(targetWorksheet.Rows.Count, dcStartDate).End(xlUp).Row
            
            Set dataRange = targetWorksheet.Range("A1").CurrentRegion
            Set dataRangeNoHeader = dataRange.Offset(1).Resize(dataRange.Rows.Count - 1)
            
            arr = dataRangeNoHeader
            
            wsConsolidatedLocations.Activate
            numRows = UBound(arr, 1) - LBound(arr, 1) + 1
            numColumns = UBound(arr, 2) - LBound(arr, 2) + 1
            
            Set consolidatedRange = wsConsolidated.Range(wsConsolidated.Cells(startRow, 2), wsConsolidated.Cells(startRow + numRows - 1, numColumns + 1))
            consolidatedRange.Select
            consolidatedRange.value = arr
            
            'write Location into column ` -start from last row
            
            lastRowInConsolidatedWorksheet = wsConsolidated.Cells(wsConsolidated.Rows.Count, 2).End(xlUp).Row
            For i = startRow To lastRowInConsolidatedWorksheet
                wsConsolidatedLocations.Cells(i, 1) = targetWorksheetName
            Next i
            
            startRow = lastRowInConsolidatedWorksheet + 1
       
        Next cell
    End With
    

End Sub

Private Sub CreatePivotTable()
Dim pt As pivotTable

    wbData.Activate
    
    Dim consolidatedRange As Range
    lastRowInConsolidatedWorksheet = wsConsolidated.Cells(wsConsolidated.Rows.Count, 2).End(xlUp).Row
    Set consolidatedRange = wsConsolidated.Range(wsConsolidated.Cells(1, 1), wsConsolidated.Cells(lastRowInConsolidatedWorksheet, 7))
    wsConsolidated.Activate
    consolidatedRange.Select
    wsDashboard.Activate
    DeleteAllPivotChartsAndTables wsDashboard

    Application.CutCopyMode = False
    

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    consolidatedRange, Version:=8).CreatePivotTable TableDestination:= _
    "Dashboard!R1C2", TableName:="ptUsageData", DefaultVersion:=8
   

    Sheets("Dashboard").Select
    Cells(1, 11).Select
    With ActiveSheet.PivotTables("ptUsageData")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("ptUsageData").pivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("ptUsageData").RepeatAllLabels xlRepeatLabels
    Application.Width = 1191.75
    Application.Height = 1000.5
    With ActiveSheet.PivotTables("ptUsageData").PivotFields("Location")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ptUsageData").PivotFields("Start Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ptUsageData").PivotFields("Start Date").AutoGroup
    ActiveSheet.PivotTables("ptUsageData").PivotFields("Quarters (Start Date)"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("ptUsageData").PivotFields("Months (Start Date)"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("ptUsageData").PivotFields("Start Date").Orientation _
        = xlHidden
    With ActiveSheet.PivotTables("ptUsageData").PivotFields("Months (Start Date)")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ptUsageData").AddDataField ActiveSheet.PivotTables( _
        "ptUsageData").PivotFields("Usage"), "Sum of Usage", xlSum
    Application.Width = 1638
    Application.Height = 1000.5
    Range("K3").Select
    ActiveSheet.PivotTables("ptUsageData").DataPivotField.PivotItems( _
        "Sum of Usage").Caption = " Usage"
    Range("L3").Select
    ActiveSheet.PivotTables("ptUsageData").CompactLayoutColumnHeader = _
        "Select Years"
    Range("K4").Select
    ActiveSheet.PivotTables("ptUsageData").CompactLayoutRowHeader = "Months"
    
 
    
    '******************************************************************************
    ' ADDED
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    consolidatedRange, Version:=8).CreatePivotTable TableDestination:= _
    "Dashboard!R1C13", TableName:="ptDemandData", DefaultVersion:=8
    '******************************************************************************
    
    
    
    
    
    With ActiveSheet.PivotTables("ptDemandData")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("ptDemandData").pivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("ptDemandData").RepeatAllLabels xlRepeatLabels
    Application.Width = 1191.75
    Application.Height = 1000.5
    With ActiveSheet.PivotTables("ptDemandData").PivotFields("Location")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ptDemandData").PivotFields("Start Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ptDemandData").PivotFields("Start Date").AutoGroup
    ActiveSheet.PivotTables("ptDemandData").PivotFields("Quarters (Start Date)"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("ptDemandData").PivotFields("Months (Start Date)"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("ptDemandData").PivotFields("Start Date").Orientation _
        = xlHidden
    With ActiveSheet.PivotTables("ptDemandData").PivotFields("Months (Start Date)")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ptDemandData").AddDataField ActiveSheet.PivotTables( _
        "ptDemandData").PivotFields("Demand"), "Sum of Usage", xlSum
    Application.Width = 1638
    Application.Height = 1000.5
    Range("K3").Select
    ActiveSheet.PivotTables("ptDemandData").DataPivotField.PivotItems( _
        "Sum of Usage").Caption = " Demand"
    Range("L3").Select
    ActiveSheet.PivotTables("ptDemandData").CompactLayoutColumnHeader = _
        "Select Years"
    Range("K4").Select
    ActiveSheet.PivotTables("ptDemandData").CompactLayoutRowHeader = "Months"
    

    
    
    Set consolidatedRange = Nothing



End Sub
Sub DeleteAllPivotChartsAndTables(ByVal ws As Worksheet)
On Error Resume Next
    Dim obj As Object
    
  
    
    ' Delete PivotCharts
    For Each obj In ws.ChartObjects
        If obj.chart.PivotLayout Is Nothing Then
            obj.Delete
        End If
    Next obj
    
    ' Delete PivotTables
    For Each obj In ws.PivotTables
        obj.TableRange1.Clear '--CHANGED 9/26
        obj.TableRange2.Clear ' Clear the PivotTable data
        obj.Delete
    Next obj
End Sub