6

I have 3 sheets with several pivot tables on each and one sheet with the source data. These 4 sheets were copied directly from another workbook. When I copied the worksheets over the source data for each pivot table was still the original source data sheet in the original workbook. Therefore, I changed each pivot table's source to the copied over raw data sheet. Whilst doing this I had to disconnect all the pivot table slicers in order to be able to change the source.

Now, this is where my problem lies; When I go back to reconnect the slicer only one of the pivot tables shows up in the list, when in fact I need to connect the slicer to several. What do I need to do?

Note: I did all of this using a macro that I created so I am open to using macros.

Community
  • 1
  • 1
Sorath
  • 543
  • 3
  • 10
  • 32

4 Answers4

8

I was able to resolve this issue with the following steps;

  1. Delete all slicers

  2. Change the Table name:

    (example here)

  3. For each pivot table, click on change data source button. The new name should already be in there so just press enter. If you didnt delete all slicers it will throw an error, indicating that it is only identifying other pivot tables with the same datasource now.

    (example here)

  4. Create a new slicer and reconnect to all tables.

Note that step 2 may not be required but that is the workflow that I followed.

Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
Dane Howarth
  • 81
  • 2
0

I had the same issue with not all pivots showing up and noticed that while the SOURCE was the same in all, the RANGE that I had selected in the source was different. I reset all the ranges in the pivot tables to be the same and all tables now showed up in the slicer for selection.

Robert
  • 1
0

I had the same issue, and fixed it using the following idea.

  1. Reassign the Pivot Tables' data sources (first define one Pivot Table source, and then copy the PivotCaches to the others):

         Sheets("nameofthesheet").Select

         ActiveSheet.PivotTables("PivotTable1").ChangePivotCache _
     ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
     "name_of_the_table")

     ActiveSheet.PivotTables("PivotTable2").CacheIndex = ActiveSheet.PivotTables("PivotTable1").CacheIndex
     ActiveSheet.PivotTables("PivotTable3").CacheIndex = ActiveSheet.PivotTables("PivotTable1").CacheIndex
     ActiveSheet.PivotTables("PivotTable4").CacheIndex = ActiveSheet.PivotTables("PivotTable1").CacheIndex

  2. Rebuild the Slicers (recording a Macro will be useful!)

Fabio Bellegard
  • 1
0

Make sure all your pivot table have the same data range, if not change the data range, when all pivot table have the same data range, then it will appear at the connection for the slicer

lim kiat tsen
  • 1
  • 1