refresh micro charts from pivot table with VBA

Question

Hi

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.

any ideas?

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

Answer 1

btw.. I also tried adding "Doevents", but that did not seem to do anything.

        Sheet3.Calculate
        DoEvents

Answer 2

hi could you please check here the link for your reference.

http://www.ozgrid.com/VBA/pivot-table-refresh.htm

Answer 3

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