Hi Everyone,

I am very new as a VBA user and try to pull together my first Macro.

What I am doing here is basically to copy all sheets from national workbook to a new one and only keep branch data, making it the branch report. I have 5 slicers connected to 3 pivot tables and all 3 pivot tables share same data. In my Macro, after I copy national data sheet, I only keep branch one I need, change data source for all three pivot tables and refresh. However, even all three PT share same data source, my slicers end up show to be able to only connect with one pivot table. If I manually go to each pivot table, choose change data source and refresh, then I go click one slicer, all the pivot table choice come up again.

There must be something wrong with my code. I hope someone could let me know the reason and guide me through that. Thank you very much.

Below is my code.

'Change data source for other 3 pivot tables'

FinalRow3 = WSBData.Cells(Rows.Count, 1).End(xlUp).Row
Set DataArea = WSBData.Range("$A$1:$S" & FinalRow3)

WBB.Activate


WSB14P.PivotTables("PivotTable 1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
Version:=xlPivotTableVersion14)
WSB14P.PivotTables("PivotTable 1").RefreshTable


' WBB.Worksheets("2013").Select
WSB13P.PivotTables("PivotTable 2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
Version:=xlPivotTableVersion14)
WSB13P.PivotTables("PivotTable 2").RefreshTable


'WBB.Sheets("Penetration").Select
WSBPentr.PivotTables("PivotTable 3").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
Version:=xlPivotTableVersion14)
WSBPentr.PivotTables("PivotTable 3").RefreshTable