Created PivotTables that have same SourceWorkSheet and same SourceRange, but manually created slicer cannot see both of them

[nashartwell1]

I have two pivotTables in the same worksheet that were created using the code Add-PivotTable -Address $Address -SourceWorkSheet $Excel."Clean Data" -SourceRange $Excel."Clean Data".Dimension.Address -PivotRows $pivotRows -PivotData @{"Effort"="Sum"} -PassThru -PivotTableName $pivotName

The only difference between them is the Address, the pivotRows, and the PivotTableName

I open the excel file that has these pivotTables and manually create a slicer for a Field that they both share by clicking on one of the pivotTables and then go to the 'PivotTable Analyze" tab and then "Insert Slicer" and choose the field to make a slicer based on.

I want this one slicer to filter both my pivotTables. I click on the Slicer and then select "Report Connections" to link the slicer to both the pivotTables and only the pivotTable that I earlier selected is shown even though both my tables share the same data source.

I then can click on both the the pivotTables and click on "Change Data Source" and just hit "OK" without modifying anything, and suddenly my slicer can see both the pivotTables.

I have to do the "Change Data Source" > "OK" on both tables for this to work

[nashartwell1]

I have also tried further updating the source of my pivot tables utilizing ComObjects with

$excelCom = New-Object -ComObject Excel.Application
$workbookCom = $excelCom.Workbooks.Open($ExcelFilePath)

$worksheetCom = $workbookCom.Worksheets.Item("Data Overview")
$worksheetCom.Activate()

worksheetCom.PivotTables(1).SourceData = $worksheetCom.PivotTables(2).SourceData
worksheetCom.PivotTables(2).SourceData = $worksheetCom.PivotTables(1).SourceData

to try to ensure they have the same source and it still does not work

[nashartwell1]

For now, I have made a workaround for this but creating a macro in Excel that does all the manual actions and having my PowerShell script add this macro to my generated Excel files and then run it.

For those who might read this and are looking for the code to do that (assuming you already have created the macro in an Excel file and exported it into your file explorer)...

$excelCom = New-Object -ComObject Excel.Application
$excelCom.Visible = $true
$workbookCom = $excelCom.Workbooks.Open($ExcelFilePath)
$worksheetCom = $workbookCom.Worksheets.Item($workSheetName)
$worksheetCom.Activate()

$xlmodule = $workbookCom.VBProject.VBComponents.Add(1)
$xlmodule.CodeModule.AddFromFile($filePathtoYourMacro)
$excelCom.Run("MacroName")

There might also be a way to use the VBA code that gets generated from the Macro and rewrite it in PowerShell and it would work. But as I just have it working right now with the Macro, I am not going to try to rewrite it.