Slicers not triggering pivots when sheets 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.
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 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?

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 can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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
Back
Top