I am new to VBA so I apologize if this is a rookie mistake. I am attempting to create several pivot tables on the same sheet with each one having their own chart. The goal is to do this every month as the data will be different monthly and the resulting pivot tables will have a dynamic range. I can create each pivot table and have their filters applied correctly with no issue. However, when I create the charts for each table, I run into errors defining the source data. The exact error is “Run-time error ‘-2147467259 (80004005)’: Method ‘SetSourceData’ of object ‘_Chart’ failed”.

The dummy data I’m using is just 2 columns with 1’s and 0’s each titled “Missing Form1” and “Missing Form2”. The source data will be the same for each pivot table. Here is the macro I wrote:

Sub Macro2()

    Dim pcLinkage As PivotCache
    Dim PivotTableName As PivotTable
    Dim PivotTableName2 As PivotTable
    Dim wsNew As Worksheet
    Dim Form1Chart As ChartObject
    Dim Form2Chart As ChartObject
    
    ' Create pivot cache
    Set pcLinkage = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=ActiveWorkbook.Worksheets("Link").Range("A1").CurrentRegion.Address)
    
    ' Add a new worksheet
    Set wsNew = Sheets.Add
    
    ' Create first pivot table
    Set PivotTableName = pcLinkage.CreatePivotTable( _
        TableDestination:=wsNew.Name & "!R1C1", TableName:= _
        "PivotTableName")
    
    ' Add data field to first pivot table
    With PivotTableName
        .AddDataField .PivotFields("Missing Form1"), _
            "Count of Missing Form1", xlCount
    End With
    
    ' Create chart for first pivot table
    Set Form1Chart = wsNew.ChartObjects.Add(Left:=300, Top:=200, Width:=550, Height:=200)
    With Form1Chart.Chart
'This works exactly as intended. In my live workbook, the filter methods and formatting are applied correctly'
        .SetSourceData Source:=PivotTableName.TableRange1
        .ChartWizard Source:=PivotTableName.TableRange1, _
            Gallery:=xlColumn, Title:="Missing Form1 Turnbacks", HasLegend:=False
    End With
    
    ' Create second pivot table
    Set PivotTableName2 = pcLinkage.CreatePivotTable( _
        TableDestination:=wsNew.Name & "!R1C4", TableName:= _
        "PivotTableName2")
    
    ' Add data field to second pivot table
    With PivotTableName2
        .AddDataField .PivotFields("Missing Form2"), _
            "Count of Missing Form2", xlCount
    End With
    
    ' Create chart for second pivot table
    Set Form2Chart = wsNew.ChartObjects.Add(Left:=300, Top:=500, Width:=550, Height:=200)
    With Form2Chart.Chart
'This is where the error occurs'
        .SetSourceData Source:=PivotTableName2.TableRange1
'I tried to use this .ChartWizard by itself but it creates a chart using the data from PivotTableName'
        .ChartWizard Source:=PivotTableName2.TableRange1, _
            Gallery:=xlColumn, Title:="Missing Form2", HasLegend:=False
    End With

End Sub

I have tried making this into a Shape and explicitly defining the range of the pivot table but run into the same error. I have tried using the .ChartWizard method, but it makes each chart connected and use PivotTableName data (same filters, same data series) instead of PivotTableName2, etc. I have also tried to create a new pivot cache for the second pivot table but I ran into the same issues.

I’m at a loss here I can’t figure out what I’m doing wrong when looking at the method libraries online or related articles here. Any help is appreciated!