Monday, May 07, 2012 3:24 PM
I have some code that loops through the items on my slicer and prints out the related sheets to PDF. I have a number of pivot tables and pivot charts connected to my slicer. Everything works perfectly except for one thing... my micro charts.
I have a set of spark lines on sheet3 that are lined up next to some Current month, YTD and full year values that are on one pivot table. Their data source is an identical (row) pivot table located outside of the print range that has a 12 month trend in the columns.
When I click through each of my slicer items everything lines up fine. Rows for the two matching pivot tables expand and contract together with the micro charts lining up just fine. But when I run my code the micro charts seem to lag and represent data from a previous item. I've tried a number of things to get it to work (sheet recalc and a time delay)... but so far nothing seems to work.
Here is my code:
Private Sub cmdPublishAlltoPDF_Click() Dim strPDFName As String Dim strFilepath As String Dim sC As SlicerCache Dim SL As SlicerCacheLevel Dim sI As SlicerItem 'set output location where the files should be saved to strFilepath = "K:\mypath\Reporting\" 'identifies the slicer to loop thru Set sC = ActiveWorkbook.SlicerCaches("slicer_Functional_Dept") Set SL = sC.SlicerCacheLevels(1) For Each sI In SL.SlicerItems sC.VisibleSlicerItemsList = Array(sI.Name)'select a slicer item strPDFName = sI.Value & ".pdf" 'use item name as the file name ' Sheet3.Calculate 're calc sheet with micro charts Application.Wait Now + TimeValue("00:00:05") 'time delay Sheets(Array("BvA Analysis", "BvA Details")).Select 'select sheets to print 'Prints the selected sheets to PDF ActiveSheet.ExportAsFixedFormat xlTypePDF, strFilepath & "\" & strPDFName, , True, False, , , False 'select only sheet with slicer Sheets("BvA Analysis").Select Next 'move on to the next slicer item MsgBox "Process complete. Files published to " & strFilepath, vbOKOnly, "Done" End Sub
Monday, May 07, 2012 3:49 PM
btw.. I also tried adding "Doevents", but that did not seem to do anything.
Wednesday, May 09, 2012 3:28 PM
hi could you please check here the link for your reference.
Wednesday, May 09, 2012 4:06 PM
Thanks for the info, but in this case the pivot tables themselves are refreshing fine the issue is that the microcharts that are referencing one of the pivot tables is not updating when the pivot table changes.
The pivot tables get refreshed when the slicer selection is changed
sC.VisibleSlicerItemsList = Array(sI.Name)'select a slicer item