Issue with PivotTables when running VBA Code to Loop through Slicers and Print to and Save PDFs - Couldn't Complete Action Because Theres Already

Question

I tried to find a previous QA on this, but I haven't been able to find a solution.

I have inherited a connected Excel spreadsheet that has one worksheet that contains a slicer with about 170 different items on it. In this case they are employee names.

The next worksheet is a scorecard, that is populated by values from 4 different pivot tables.

So you select an employee in the slicer, the pivot tables filter to the info for that employee, and the information displays on the formatted scorecard.

Currently, the process is to click each employee on the slicer, one at a time, then go to the scorecard worksheet, print to pdf, and then save the pdf with the file name being the employee's name.

What I am wanting to do is use VBA to automate this process by clicking one button, it will loop through the slicer, select each employee, print to pdf the associated scorecard, and save the file in a directory on my PC using a cell with the employee's name as the file name.

I found some code on a previous QA on this site that I have been trying to adapt for this purpose.

It seems like it is working somewhat as it will correctly save a PDF of the first employee. When it is supposedly going to the next slicer item, it throws an error which is basically (not exact wording) '"we couldn't complete the action for the PivotTable" "in the sheet" "because there's already a PivotTable".

I can't figure out how to fix this error. All 4 pivot tables are on the same worksheet, and are side by side with at least one column in between them. I cycled through the slicer, and all items seem to have the same number of columns. The pivots only show one employee at a time.

Any ideas?

Sub LoopThroughSlicerAndPrintPDF()
    Dim wsPrint As Worksheet
    Dim wsSlicer As Worksheet
    Dim slicerCache As slicerCache
    Dim slicerItem As slicerItem
    Dim pdfPath As String
    Dim pdfName As String
    Dim cellValue As String
    
    ' Set your worksheets
    Set wsSlicer = ThisWorkbook.Sheets("Choose_an_Employee")
    Set wsPrint = ThisWorkbook.Sheets("Scorecard")
    
    ' Set your slicer cache
    Set slicerCache = ThisWorkbook.SlicerCaches("Slicer_Employee_Full_Name1")
    
    ' Loop through each slicer item
    For Each slicerItem In slicerCache.SlicerItems
        ' Select the slicer item
        slicerItem.Selected = True
        
        ' Get the value from the cell to name the PDF
        cellValue = wsPrint.Range("C4").Value ' Change "A1" to your specific cell
        
        ' Define the PDF path and name
        pdfName = cellValue & ".pdf"
        pdfPath = "C:\Scorecards" & pdfName
        
        ' Print the worksheet to PDF
        wsPrint.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath, Quality:=xlQualityStandard
        
        ' Deselect the slicer item
        slicerItem.Selected = False
    Next slicerItem
End Sub

The logic of how the slicerItem is selected seems sketchy to me. Once you deselect every item in a slicer, it defaults to all items being selected (that how the GUI works anyway). It seems to me that you would want to make sure all items were selected and then iterate through them to deselect each one except the one you want. — Frank Ball
– Frank Ball, Commented Oct 25, 2024 at 18:09

Michal · Accepted Answer · 2024-10-25 17:37:04Z

0

The most likely cause of the the error you're encountering is that PivotTables are getting refreshed too quickly when changing slicer items, which might be causing the Excel engine to conflict with the PivotTables being updated simultaneously.

Try adding a delay when looping thru the items:

 If slicerItem.Selected = False Then
            slicerItem.Selected = True

            DoEvents
            Application.Wait Now + TimeValue("00:00:01")

answered Oct 25, 2024 at 17:37

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue with PivotTables when running VBA Code to Loop through Slicers and Print to and Save PDFs - Couldn't Complete Action Because Theres Already

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related