VBA, Pivot Show Detail, Create to the right not the left

Mattador · Jan 28th 2017

Hi,

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:

Code

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

Display More

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.

Thanks

Matt

gijsmo · Jan 29th 2017

Re: VBA, Pivot Show Detail, Create to the right not the left

If you want to have the detail sheet to the immediate right of Sheet1 try:

Code

Sheet1.Range("E9").ShowDetail = True 
  ActiveSheet.Name = cc & " | " & Format(Now, "mm-ss") 
  ActiveSheet.Move After:=Sheet1

if you want to move the newly created sheet to the very end try:

Code

Sheet1.Range("E9").ShowDetail = True 
  ActiveSheet.Name = cc & " | " & Format(Now, "mm-ss") 
  ActiveSheet.Move After:=Sheets(ThisWorkbook.Sheets.Count)

Mattador · Jan 29th 2017

Re: VBA, Pivot Show Detail, Create to the right not the left

Perfect, thanks.

VBA, Pivot Show Detail, Create to the right not the left

Participate now!

Share