Slicers wont trigger all pivots when data spreadsheets are hidden

[Mitheral]

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

 

[SpillerBD]

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]

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]

Maybe after making Visible, force a Excel.Workbook.RefreshAll
or just before re-hiding those sheets...? or both?