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

[Steve48]

This is the code in an Excel user form. I have two pivot tables ptUsageData & ptDemandData. I have two subs CreatePivotTable and CreatePivotCharts. In cmdPivot_Click if the Subs are called consecutively as shown I get an error
at the line chart.chart.SetSourceData Source:=pt.TableRange1 (The error is "SetSourceData of Object _chart failed") in the Sub CreateChart. However if the pivot tables exist and I run Sub cmdCharts_Click which calls CreateCharts directly without first calling CreatePivotTable, then the charts get created without error. I want to do it the first way and have been unable to determine why the error is happening TIA

Option Explicit
Dim chart As ChartObject
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 cmdPivot_Click()

    Call CreatePivotTable
    Call CreatePivotCharts
End Sub
Private Sub cmdCharts_Click()

    Call CreatePivotCharts
    Unload Me
    frmControlPanel.Show
    
End Sub

Private Sub cmdControlPanel_Click()
    Unload Me
    frmControlPanel.Show
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"
    
 
    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 CreatePivotCharts()
    Dim pt As pivotTable
    
    Dim filterField As PivotField
    Dim filterValue As String
    Dim filterFieldString As String
    Dim arrChartPosition As Variant
    Dim titleText As String
    Dim chartType As Integer

    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)
    '***************************************
    titleText = "Usage Data"
    arrChartPosition = Array(CHARTS_LEFT, CHARTS_TOP, CHART_WIDTH, CHART_HEIGHT)
    filterFieldString = "Location"
    chartType = xlColumnClustered
    Call CreateChart(pt, "chtUsageColumn", arrChartPosition, filterFieldString, filterValue, titleText, chartType)
    '***************************************
    '   Usage Line Chart (II)
    '***************************************
    titleText = "Usage Data (YOY)"
    chartTop = CHARTS_TOP + CHART_HEIGHT + verticalSpacer
    arrChartPosition = Array(CHARTS_LEFT, chartTop, CHART_WIDTH, CHART_HEIGHT)
    filterFieldString = "Location"
    chartType = xlLine
    Call CreateChart(pt, "chtUsageLine", arrChartPosition, filterFieldString, filterValue, titleText, chartType)
    Set pt = Nothing
    
    '***************************************
    '   Initial Demand Pivot Table
    '***************************************
    ' 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
    '***************************************
    '   Demand Column Chart (III)
    '***************************************
    ' Create a new chart and assign a name to it
    titleText = "Demand Data"
    chartLeft = CHARTS_LEFT + CHART_WIDTH + horizontalSpacer
    arrChartPosition = Array(chartLeft, CHARTS_TOP, CHART_WIDTH, CHART_HEIGHT)
    filterFieldString = "Location"
    chartType = xlColumnClustered
    Call CreateChart(pt, "chtDemandColumn", arrChartPosition, filterFieldString, filterValue, titleText, chartType)

      
   '***************************************
    '   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
    ' End Set the pivot field object
    
    ' Create a new chart and assign a name to it
    titleText = "Demand Data (YOY)"
    chartLeft = CHARTS_LEFT + CHART_WIDTH + horizontalSpacer
    chartTop = CHARTS_TOP + CHART_HEIGHT + verticalSpacer
    arrChartPosition = Array(chartLeft, chartTop, CHART_WIDTH, CHART_HEIGHT)
    filterFieldString = "Location"
    chartType = xlLine
    Call CreateChart(pt, "chtDemandLine", arrChartPosition, filterFieldString, filterValue, titleText, chartType)
    Set pt = Nothing

    message = "Dashboard Updated"
    frmMessage.Show
    
    
End Sub

Sub CreateChart(ByVal pt As pivotTable, ByVal chartName As String, ByVal arrChartPosition As Variant, ByVal filterFieldString As String, ByVal filterValue As String, ByVal titleText As String, ByVal chartType As Integer)
    Dim chart As ChartObject
   ' Create a new chart and assign a name to it
    Set chart = ActiveSheet.ChartObjects.Add(Left:=arrChartPosition(0), Top:=arrChartPosition(1), Width:=arrChartPosition(2), Height:=arrChartPosition(3))
    chart.Name = chartName
    
  
    ActiveSheet.ChartObjects(chartName).Activate
    ActiveChart.PlotArea.Select
    ActiveChart.ChartArea.Select
    ActiveChart.chartType = chartType
    
    ' 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(filterFieldString).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
 
     chart.chart.HasTitle = True
     chart.chart.ChartTitle.Text = titleText
    
    ' Refresh the chart to apply the filter
    chart.chart.Refresh
    Set chart = Nothing
End Sub





Private Sub UserForm_Initialize()
    Me.Height = 700
    Me.Width = 800
    SetUp
    If wsConsolidated.Cells(2, 1) = "" Then
        cmdPivot.Enabled = True
        cmdCharts.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
        cmdCharts.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


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