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