Why are my slicers not appearing?

[SolemnSpirit]

Hi all,

I have a workbook that has a data sheet and two sheets that each have a different pivot table on them.

The pivot tables are created using vba code and, after initially struggling to get the 2nd pivot table to appear, this is now working. However I want to have slicers attached to these pivots (each only affecting a single table) and while the code I have seems to be correct excel will only create one set of slicers and not the other. Originally it was creating only the 1st set of slicers and not the 2nd but now somehow it is creating the 2nd and not the 1st.

The forum will not allow me to copy the full code for both pivot/slicer sets into this post so I have attached a copy of the workbook. The code is in the modules Sub_Make_1st_Pivot and Sub_Make_2nd_Pivot. I have also copied the code for the 1st pivot/slicer set below. The 2nd set is basically the same code but with PivotTableName changed and PSheet changed to Sheet3.

I appreciate you taking the time to look at this and for any help/guidance you may have.

Many thanks,
Chris

1st Pivot/Slicer Set:

Sub MakePivot1()

' |-------- PIVOT TABLE 1 START --------|
ProgressForm "Reporting"

Dim PSheet As Worksheet, DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastSourceRow As Long, LastSourceCol As Long

On Error Resume Next
Set PSheet = Worksheets("Summary")
PSheet.Activate
Set DSheet = Worksheets("Data")
PivotTableName = "PivotTable1"
LastSourceRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastSourceCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastSourceRow, LastSourceCol)
Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(3, 7), TableName:=PivotTableName)

ProgressForm "None", "Creating pivot table (" & PivotTableName & ")"

With PSheet
With .PivotTables(PivotTableName).PivotFields("Employee Name")
.Orientation = xlRowField
.Position = 1
.LayoutBlankLine = True
.Function = xlNone
End With
With .PivotTables(PivotTableName).PivotFields("Blank For Spacing")
.Orientation = xlRowField
.Position = 2
.LayoutBlankLine = True
.Function = xlNone
End With
With .PivotTables(PivotTableName).PivotFields("Charge Code (Full)")
.Orientation = xlRowField
.Position = 3
.LayoutBlankLine = True
.Function = xlNone
End With
With .PivotTables(PivotTableName).PivotFields("Panel No. (Full)")
.Orientation = xlRowField
.Position = 4
.LayoutBlankLine = True
.Function = xlNone
End With
With .PivotTables(PivotTableName).PivotFields("Total Time")
.Orientation = xlDataField
.Position = 1
.LayoutBlankLine = True
.Function = xlSum
.NumberFormat = "[h]:mm:ss"
.Name = "Sum of Total Time"
End With

.PivotTables(PivotTableName).ShowTableStyleRowStripes = False ' Turn off alternating colour rows
.PivotTables(PivotTableName).TableStyle2 = "NewPivotStyle" ' Set colour to custom style
.PivotTables(PivotTableName).SubtotalLocation xlAtBottom ' Set subtotals to appear at bottom of groups
.PivotTables(PivotTableName).PivotFields("Blank For Spacing").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotTables(PivotTableName).PivotFields("Charge Code (Full)").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotTables(PivotTableName).PivotFields("Panel No. (Full)").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotTables(PivotTableName).ShowDrillIndicators = False ' Turn off +/- buttons
.PivotTables(PivotTableName).DisplayFieldCaptions = False ' Turn off headers
.PivotTables(PivotTableName).HasAutoFormat = False ' Stop pivot from resizing on update
.Columns("G").ColumnWidth = 39.71
End With
' |-------- PIVOT TABLE 1 END --------|
End Sub

 

[Chihiro]

Your zip file is empty. I'd recommend re-uploading with workbook

 

[SolemnSpirit]

Hmm not sure how that happened. Can't seem to edit the 1st post so I've attached the zip to this one. Should have the file in this time.

 

[Chihiro]

Hmm, went through your code, but unable to test it on my machine, since there's no data to work with (also had to convert API call to work on 64 bit install).

At any rate, there is no code there for adding slicer to 1st Pivot Table in your sample.

There could be a few reason why only 1 appears.

1. Slicer Names are not distinct
2. SlicerCache is shared


From your code, both your pivots are made using same data, so it can really be made from single cache. Rather than from 2 separate cache.

I'd do it like below...
Make your first pivot. Then copy that pivot over to Sheet3. Make adjustments to fields. This way no redundant caches are created.

Then make your first slicer. Disconnect link to 2nd pivot. Then copy your slicer into 2nd location. Connect to 2nd pivot and then disconnect from first.
Repeat process for each slicer.

EDIT: Oh one more thing. I'd remove "On Error Resume Next" from your code to debug. Unless you know the exact error that code will cause and there are no other ways to handle it. You should not use that statement. Instead, use error trap or some other means to deal with it. It will make debugging code so much easier.

 

[SolemnSpirit]

Not sure how I missed the pivot code out when I copied it over but there we are. It turns out it was the slicer names as you said. All fixed now.

Thanks Chihiro !