Expanding pivot table

[iazcac]

Hi,

I have been working on a pivot table project for some time and have just finished it (thanks in part to this forum )

However, I have one little niggle I would like to get sorted.... When you double click on some of the summarized data on the pivot table it creates a new sheet listing the entire data

I like this feature, but I do not want lots of extra sheets. Can I get this data to open in a new workbook each time?

Thanks
C

 

[Derek Brown]

No, it cannot be done - drill down is a feature of Pivot Tables and is behaving as designed.
I advise people to delete the sheet as soon as they no longer need it to avoid cluttering the workbook.
However, I also like to have a bit of VBA code in the Workbook_Open event to delete any sheet who first five characters are "Sheet". If anyone wants to keep a sheet, then they must rename it.

 

[Tom Urtis]

When you double click on some of the summarized data on the pivot table it creates a new sheet listing the entire data.

I like this feature, but I do not want lots of extra sheets. Can I get this data to open in a new workbook each time?

Yes this is possible.

Find the small Excel icon just to the left of the File menu item on the menu bar. Right click on that icon, left click on View Code, and paste this into the large white area that is the Workbook module:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call DrillDownDefault
End Sub

While you are in the VBE, press Ctrl+R to show the VBAProject window. For your workbook, find the worksheet object name that is the worksheet module for the sheet where your pivot table exists. It will look something like "Sheet1(Sheet1)" depending on what the sheet tab is named which would be inside the parentheses. Right click on that sheet object name, left click on View Code, and paste this into the large white area that is the worksheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
CS = ActiveSheet.Name
Application.ScreenUpdating = False
Dim LPTR&
With ActiveSheet.PivotTables(1).DataBodyRange
LPTR = .Rows.Count + .Row - 1
End With
Dim PTT As Integer
On Error Resume Next
PTT = Target.PivotCell.PivotCellType
If Err.Number = 1004 Then
Err.Clear
Cancel = True
End If
Application.ScreenUpdating = True
End Sub

Finally, while still in the VBE, from the menu, click Insert > Module and paste this into that new module:

Public CS$

Sub DrillDownDefault()
Dim asn$
asn = ActiveSheet.Name
With Application
.ScreenUpdating = False
Workbooks.Add 1
ThisWorkbook.Worksheets(asn).Range("A1").CurrentRegion.Copy Range("A1")
ThisWorkbook.Activate
.DisplayAlerts = False
Sheets(asn).Delete
.DisplayAlerts = True
Sheets(CS).Select
.ScreenUpdating = True
End With
End Sub

Finally, press Alt+Q to return to the worksheet.

Now when you double click onto the Data area of the pivot table, the drill down info will be in its own workbook without a new sheet created.

The only issue needing to be addressed, which I did not do in code here because you did not specify, is if you want to save and name those individual workbooks. Go ahead and do that yourself in code, or post back if you don't know how.

 

[Magerator]

Yes this is possible.

Find the small Excel icon just to the left of the File menu item on the menu bar. Right click on that icon, left click on View Code, and paste this into the large white area that is the Workbook module:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call DrillDownDefault
End Sub

While you are in the VBE, press Ctrl+R to show the VBAProject window. For your workbook, find the worksheet object name that is the worksheet module for the sheet where your pivot table exists. It will look something like "Sheet1(Sheet1)" depending on what the sheet tab is named which would be inside the parentheses. Right click on that sheet object name, left click on View Code, and paste this into the large white area that is the worksheet module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
CS = ActiveSheet.Name
Application.ScreenUpdating = False
Dim LPTR&
With ActiveSheet.PivotTables(1).DataBodyRange
LPTR = .Rows.Count + .Row - 1
End With
Dim PTT As Integer
On Error Resume Next
PTT = Target.PivotCell.PivotCellType
If Err.Number = 1004 Then
Err.Clear
Cancel = True
End If
Application.ScreenUpdating = True
End Sub

Finally, while still in the VBE, from the menu, click Insert > Module and paste this into that new module:

Public CS$
 
Sub DrillDownDefault()
Dim asn$
asn = ActiveSheet.Name
With Application
.ScreenUpdating = False
Workbooks.Add 1
ThisWorkbook.Worksheets(asn).Range("A1").CurrentRegion.Copy Range("A1")
ThisWorkbook.Activate
.DisplayAlerts = False
Sheets(asn).Delete
.DisplayAlerts = True
Sheets(CS).Select
.ScreenUpdating = True
End With
End Sub

Finally, press Alt+Q to return to the worksheet.

Now when you double click onto the Data area of the pivot table, the drill down info will be in its own workbook without a new sheet created.

The only issue needing to be addressed, which I did not do in code here because you did not specify, is if you want to save and name those individual workbooks. Go ahead and do that yourself in code, or post back if you don't know how.

Hello,

I found this old thread and it's about only one I've come across when trying to work around my problem.

What I would want is that I could get pivot details to open in a new worksheet. The code above does that , but I can't combine it with the code I have now for removing "unwanted" columns.
Could you please help to get those codes somehow together.

1) pivot details would open up to a new worksheet
2) clear unwanted columns

The code I'm using for deleting unwanted columns is below :

Sub Hylkyehdotus()
'
' Hylkyehdotus Macro
'
    Dim rngArea As Range
    With Sheets("TOY Mill Stocks").PivotTables("PivotTable2").TableRange1
        .Cells(.Cells.Count).ShowDetail = True
        For Each rngArea In Range("B:B,E:G,J:J,L:AZ,BB:BG").Areas
            rngArea.Delete
        Next rngArea
    End With
 
End Sub

Thanking you,
Jyri

 

[Tom Urtis]

This speaks to deleting part ofa pivottable subset, so you should be able to do it if you copy and paste special for values the subset. However, doing what you say you want to do would not give an accurate portrayal of the subset data, so why do you want to do this?

 

[Magerator]

This speaks to deleting part ofa pivottable subset, so you should be able to do it if you copy and paste special for values the subset. However, doing what you say you want to do would not give an accurate portrayal of the subset data, so why do you want to do this?

Hello,

My problem solved during the weekend and yes I really need to delete some of the unwanted columns from the pivot details to give a summary kind of view of the situation.
Only thing I'm left with is sending mail to one user, when macro is run.
That way she will get notified that "new waste proposal has been made".

Thank you anyway for taking time to answer to my e-mail.
Jyri

 

[Tom Urtis]

That way she will get notified that "new waste proposal has been made".

How 'bout that -- my code benefitted a waste proposal. Of course I am funning with you; good to know you got what you needed, good luck with your project.