I have a workbook of reports that I update for a list of departments. I iterate through the department list, with formulas looking to the cell I update to update the sheets. Some of the sheets however are pivot tables that are updated via Slicers. Those pivot tables are connected via an OLAP connection.
I have a macro where it selects the slicers for the departments & create copies of the workbook and works properly, but when I iterate the same way, only changing the step where I print to PDF instead of creating a new workbook it seems the pivot tables aren't selecting the slicers. It will choose the first one properly, but every subsequent one doesn't refresh properly. Even weirder, the saving to new files all happens a little after the slicer selection.
I know there's a lot to do with waiting for the connections to refresh and continuing the macro, but the workbook version works fine, so I'm scratching my head why the PDF version doesn't beyond the workbook version taking longer and therefore giving the pivot time to refresh, but all that happens before the section that creates the files.
The section that chooses the slicers is below. Note that I can run this report at two levels, so I have different slicers and the logic is to unselect the higher level if the user chooses so, whereas the other way runs for the more granular level.
If Scenario = "Department" Then
'Department
Mainbook.SlicerCaches("Group"). _
ClearManualFilter
Mainbook.SlicerCaches("Department"). _
VisibleSlicerItemsList = Array( _
"[Department].&[" & Code & "]")
Mainbook.SlicerCaches("Department2"). _
VisibleSlicerItemsList = Array( _
"[Department].&[" & Code & "]")
Else
'Group
Mainbook.SlicerCaches("Department1"). _
ClearManualFilter
Mainbook.SlicerCaches("Department2"). _
ClearManualFilter
Mainbook.SlicerCaches("Group"). _
VisibleSlicerItemsList = Array( _
"[Group].&[" & Code & "]")
End If
I added a section to calculate and wait after the slicer selection, but it still doesn't even select the slicer options seeming to skip over the section of the macro entirely after the first iteration:
Calculate
Application.Wait Now + TimeValue("00:00:20")
Sub GeneratePDFS()
DefineVars
On Error GoTo FireExit
For Index = 2 To LastCode Step 1
Driver.Value = List.Cells(Index, 2).Text
UpdatePivots 'Change pivot slicers
PrintToPDF 'Prints PDFs without needing workbooks generated
NextIndex:
Next Index
FireExit:
End Sub
After chewing on this for a while the issue didn't lie with the pivots not refreshing fast enough. Therefore, (DoEvents, Application.Wait, Calculate, etc.) were not yielding results.
As I saved a PDF manually, excel prompted me to save the workbook and then took me out of the workbook after saving. In the debugging mode (F8), I realized at the PDF section of the macro it just ran the entire population on it's own without my interference, and in fact trying to stop it didn't work and I realized it was because something about the process of saving to PDF takes us out of the workbook.
The macro would run in an 'autopilot' mode where it did all the logic and everything accordingly and kept the sheets I was printing selected. Updating cells, letting the formulas refresh etc. except the objects such as the pivot slicers weren't being select after the first iteration.
So I had the idea of re-activating the workbook before starting the next iteration to kind of 'reset' the status of the workbook and it worked! I just picked the sheet that had the inputs driving the workbook as my reset-point because that's where I wanted to land after it was done.
Sub GeneratePDFS()
DefineVars
On Error GoTo FireExit
For Index = 2 To LastCode Step 1
Driver.Value = List.Cells(Index, 2).Text
UpdatePivots 'Change pivot slicers
PrintToPDF 'Prints PDFs without needing workbooks generated
ThisWorkbook.Sheets("Instructions").Activate
NextIndex:
Next Index
FireExit:
End Sub