The drill-down code by Mr. Tom Urtis posted on http://www.mrexcel.com/forum/showthread.php?t=289427
is wonderful. The only problem Im having so far I guess the code somewhat affects the adding of new sheet. I tried in a fresh workbook, I made a pivot table, and double clicked on the pivot table to run the drill-down code below and it's all fine. It's just that I cannot add a new sheet to my workbook. Im using excel 2007.
"Step 1
Place this code in your workbook module. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
Code:<o></o>
Private Sub Workbook_NewSheet(ByVal Sh As Object) <o></o>
Call DrillDownDefault <o></o>
End Sub<o></o>
Step 2
While in the VBE, place this in the worksheet module of the sheet that holds the pivot table: <o></o>
Code:<o></o>
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) <o></o>
Dim PTT As Integer <o></o>
On Error Resume Next <o></o>
PTT = Target.PivotCell.PivotCellType <o></o>
If Err.Number = 1004 Then <o></o>
Err.Clear <o></o>
Else <o></o>
CS = ActiveSheet.Name <o></o>
End If <o></o>
End Sub<o></o>
Step 3
Also while in the VBE, place this in a standard VBA module: <o></o>
Code:<o></o>
Public CS As String <o></o>
<o></o>
Sub DrillDownDefault() <o></o>
With Application <o></o>
.ScreenUpdating = False <o></o>
Dim LR As Long <o></o>
LR = Sheets(CS).Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2 <o></o>
Range("A1").CurrentRegion.Copy Sheets(CS).Cells(LR, 1) <o></o>
.DisplayAlerts = False <o></o>
ActiveSheet.Delete <o></o>
.DisplayAlerts = True <o></o>
Sheets(CS).Select <o></o>
.ScreenUpdating = True <o></o>
End With <o></o>
End Sub<o></o>
Step 4
Press Alt+Q to return to the worksheet.
Now, as you double-click the Data section of the pivot table, that target cell's drill-down dataset will be stacked vertically in order of the drill-downs, below and on the same sheet as the pivot table.
Another cool feature:
If, after creating a drill-down data set, yo no longer want to see it on that sheet, simply double click any cell in that data set's range and it will be deleted from the sheet."
Your help or any tweaks on the code above so as not to affect the adding a new sheet function is very much appreciated.
is wonderful. The only problem Im having so far I guess the code somewhat affects the adding of new sheet. I tried in a fresh workbook, I made a pivot table, and double clicked on the pivot table to run the drill-down code below and it's all fine. It's just that I cannot add a new sheet to my workbook. Im using excel 2007.
"Step 1
Place this code in your workbook module. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>Code:<o
></o>Private Sub Workbook_NewSheet(ByVal Sh As Object) <o
></o>Call DrillDownDefault <o
></o>End Sub<o
></o>Step 2
While in the VBE, place this in the worksheet module of the sheet that holds the pivot table: <o
></o>Code:<o
></o>Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) <o
></o>Dim PTT As Integer <o
></o>On Error Resume Next <o
></o>PTT = Target.PivotCell.PivotCellType <o
></o>If Err.Number = 1004 Then <o
></o>Err.Clear <o
></o>Else <o
></o>CS = ActiveSheet.Name <o
></o>End If <o
></o>End Sub<o
></o>Step 3
Also while in the VBE, place this in a standard VBA module: <o
></o>Code:<o
></o>Public CS As String <o
></o><o
></o>Sub DrillDownDefault() <o
></o>With Application <o
></o>.ScreenUpdating = False <o
></o>Dim LR As Long <o
></o>LR = Sheets(CS).Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2 <o
></o>Range("A1").CurrentRegion.Copy Sheets(CS).Cells(LR, 1) <o
></o>.DisplayAlerts = False <o
></o>ActiveSheet.Delete <o
></o>.DisplayAlerts = True <o
></o>Sheets(CS).Select <o
></o>.ScreenUpdating = True <o
></o>End With <o
></o>End Sub<o
></o>Step 4
Press Alt+Q to return to the worksheet.
Now, as you double-click the Data section of the pivot table, that target cell's drill-down dataset will be stacked vertically in order of the drill-downs, below and on the same sheet as the pivot table.
Another cool feature:
If, after creating a drill-down data set, yo no longer want to see it on that sheet, simply double click any cell in that data set's range and it will be deleted from the sheet."
Your help or any tweaks on the code above so as not to affect the adding a new sheet function is very much appreciated.
