I have some VBA to automate the pivot drill down as part of a larger VBA project and I was hoping for some guidance.
Essnetially the code works, but it creates sheets to the left of the original pivottable and I want to create sheets gonig to the right.
The code, and it does work is as follows:
Sub Drill_on_pivot() Dim cc As Long Dim peri As Long Dim pt As PivotTable cc = Sheet1.Range("H5").Value peri = Sheet1.Range("H6").Value On Error Resume Next 'Assigning the Pivot table fields to cell values Set pt = Sheet1.PivotTables("PivotTable1") 'This updates and refreshes the pivot table With pt.PivotFields("Cost Centre") .ClearAllFilters .CurrentPage = Sheet1.Range("H5").Value End With With pt.PivotFields("Period") .ClearAllFilters .CurrentPage = Sheet1.Range("H6").Value End With pt.RefreshTable On Error GoTo 0 Sheet1.Range("E9").ShowDetail = True ActiveSheet.Name = cc & " | " & Format(Now, "mm-ss") Cells(1).Select Sheet1.Activate End Sub
I think I just need to add something to get it to output to the right.
Hopefully this is very simple and I appreciate your help.