Slicers wont trigger all pivots when data spreadsheets are hidden

Mitheral

New Member
Joined
May 27, 2018
Messages
3
I hope someone can help with this issue. Using excel 2013 and I have a macro that triggers / synchronises several slicers that select the data in many pivots (approx. 4 per slicer).
The macro then goes on and renders and publishes the report in PDF format. I have hidden all sheets except the "option selection page" (uses direct X buttons and links to the slicers via the macro)

The problem is:
When the spread sheets containing pivots and slicers are hidden, the slicers only trigger one in four pivots, hence only 25% of the pivots change. When the sheets aren't hidden or the slicers are manually triggered, the application works like a charm.

Would slicer caching be the issue and if so, has anyone got a fix?
or

is there a line of code that can fix this?

Sample code as follows:

Private Sub Baradine_Click()
On Error GoTo ErrorHandler
User_Response = InputBox("Preparing Baradine HC QC Scorecard. Click [OK] if you wish to proceed otherwise [N] to change facility.")

If User_Response <> "N" And User_Response <> "n" Then

ThisWorkbook.Worksheets("Data").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Slicers").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Performance Scorecard").Visible = xlSheetVisible


ActiveWorkbook.SlicerCaches("Slicer_Quality_and_Safety_Facility"). _
VisibleSlicerItemsList = Array( _
_
"[Quality and Safety Facility].[Facility Fullname].&[Western NSW LHD, Central, Baradine]" _
)

ActiveWorkbook.SlicerCaches("Slicer_Facility").VisibleSlicerItemsList = Array( _
_
"[Facility].[Facility Fullname].&[Western NSW LHD, Central, Baradine]")

ActiveWorkbook.SlicerCaches("Slicer_Quality_and_Safety_Complaints_Facility"). _
VisibleSlicerItemsList = Array( _
_
"[Quality and Safety Complaints Facility].[Facility Fullname].&[Western NSW LHD, Central, Baradine]" _
)
ActiveWorkbook.SlicerCaches("Slicer_Quality_and_Safety_ED_Comms_Facility"). _
VisibleSlicerItemsList = Array( _
_
"[Quality and Safety ED Comms Facility].[Facility Fullname].&[Western NSW LHD, Central, Baradine]" _
)

ActiveWorkbook.SlicerCaches("Slicer_Quality_and_Safety_FRIH_Facility"). _
VisibleSlicerItemsList = Array( _
_
"[Quality and Safety FRIH Facility].[Facility Fullname].&[Western NSW LHD, Central, Baradine]" _
)

ActiveWorkbook.SlicerCaches("Slicer_Quality_and_Safety_HAPI_Facility"). _
VisibleSlicerItemsList = Array( _
_
"[Quality and Safety HAPI Facility].[Facility Fullname].&[Western NSW LHD, Central, Baradine]" _
)
ActiveWorkbook.SlicerCaches( _
"Slicer_Quality_and_Safety_Perineal_Lacerations_Facility"). _
VisibleSlicerItemsList = Array( _
_
"[Quality and Safety Perineal Lacerations Facility].[Facility Fullname].&[Western NSW LHD, Central, Baradine]" _
)
ActiveWorkbook.SlicerCaches("Slicer_Quality_and_Safety_SSI_Facility1"). _
VisibleSlicerItemsList = Array( _
_
"[Quality and Safety SSI Facility].[Facility Fullname].&[Western NSW LHD, Central, Baradine]" _
)

ActiveWorkbook.SlicerCaches("Slicer_Quality_and_Safety_VTE_Facility"). _
VisibleSlicerItemsList = Array( _
_
"[Quality and Safety VTE Facility].[Facility Fullname].&[Western NSW LHD, Central, Baradine]" _
)

Sheets("Data").Select
Month_Heading = ActiveSheet.Cells(21, "A")

File_Name = "Baradine - Health Care and Qualilty Committee Monthly Scorecard - " & Month_Heading & ".pdf"
Report_Heading = "Baradine Multi-Purpose Service - " & Month_Heading

Sheets("Performance Scorecard").Select
ActiveSheet.Cells(1, "A") = Report_Heading

Sheets(Array("Performance Scorecard")).Select
Sheets("Performance Scorecard").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=File_Name, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

ThisWorkbook.Worksheets("Data").Visible = xlSheetHidden
ThisWorkbook.Worksheets("Slicers").Visible = xlSheetHidden
ThisWorkbook.Worksheets("Performance Scorecard").Visible = xlSheetHidden

Close_Excel_Report
End If

ProcedureDone:
Exit Sub
ErrorHandler:

Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
Resume ProcedureDone
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,782
I don't understand the idea of using VBA to link the Pivots with the Slicers.
The slicers can be linked directly. Right Click on the slicer and select Report Connections...
 

Mitheral

New Member
Joined
May 27, 2018
Messages
3
I don't understand the idea of using VBA to link the Pivots with the Slicers.
The slicers can be linked directly. Right Click on the slicer and select Report Connections...

This report has 12 different slicers form multiple odc connections, so to save the end user having to trigger 12 individual slicers, the active X button on the slicer page does it for them.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,782
Maybe after making Visible, force a Excel.Workbook.RefreshAll
or just before re-hiding those sheets...? or both?
 

Forum statistics

Threads
1,172,023
Messages
5,878,780
Members
433,370
Latest member
mcleven

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top