pivot tables and showdetail/drill down

[charlie1105]

Hi all,

just wondering if anyone knows if it's possible to get a drill down of multiple cells from a pivot table into the same sheet easily, as when I use

    Range("BE23:BE26").Select
    Selection.ShowDetail = True

it only pulls out the first drill down (i.e. the equivalent of double clicking BE23)

I'm sure there's a work around I can write which performs each drill down separately then combine the sheets, but it's probably going to end up fairly longwinded!

If anyone knows of an easier way, please let me know

Cheers

Charlie

 

[BrianB]

I have used this method to print separate drilldowns. Just record a macro of the DoubleClick process.

However, if you just need to see data on screen, why not just use AutoFilter on the original data ?

 

[charlie1105]

Unfortunatly I dont think this will work - let me give you an example of what I'm doing, but I think the easiest way to do this is by using pivots and drill down. Note this is just one example of what I'm trying to achieve, I have many macros that all do something fairly similar.

I have a form with tickboxes for each day of the week, and when the done button is clicked it will produce a report of any transactions that have been posted on any of the selected days.

I'm creating a separate column which returns the weekday (in text, i.e. Monday, Tuesday etc).

It's very easy to get a drill down to one day, e.g. all transactions posted on a saturday, but am having problems if the user selects, for example, monday, wednesday and friday (I need a single list containing all the transactions on any of these days).

There are of course a number of ways of doing this, but when using very large amounts of data a pivot is by far the quickest way, which is why I'm looking to do it this way.

I'm thinking the only way I can achieve this is by extracting all the Mondays, all the Wednesdays and all the Fridays separatly (using show detail) and then combine the sheets, all from within VBA code

Hope this helps explain what I'm looking for

 

[BrianB]

Then perhaps :-

Sub test()
    '- show detail for selected pivot table values
    For Each c In Selection
        c.ShowDetail = True
    Next
End Sub

 

[Tom Urtis]

just wondering if anyone knows if it's possible to get a drill down of multiple cells from a pivot table into the same sheet easily. If anyone knows of an easier way, please let me know

Here is how that can be accomplished.

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.

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

Step 2
While in the VBE, place this in the worksheet module of the sheet that holds the pivot table:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
Dim PTT As Integer 
On Error Resume Next 
PTT = Target.PivotCell.PivotCellType 
If Err.Number = 1004 Then 
Err.Clear 
Else 
CS = ActiveSheet.Name 
End If 
End Sub

Step 3
Also while in the VBE, place this in a standard VBA module:

Public CS As String 

Sub DrillDownDefault() 
With Application 
.ScreenUpdating = False 
Dim LR As Long 
LR = Sheets(CS).Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2 
Range("A1").CurrentRegion.Copy Sheets(CS).Cells(LR, 1) 
.DisplayAlerts = False 
ActiveSheet.Delete 
.DisplayAlerts = True 
Sheets(CS).Select 
.ScreenUpdating = True 
End With 
End Sub

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.

 

[charlie1105]

Wow thanks Tom, this is really quite cool!

Need to have a little play with it, as the pivot table is created from code into a new sheet, so I can't place code into the "this worksheet" as that sheet doesn't exist at the time the code is executed, but I'm sure I can make it work for me!

And also the code will be run on a number of workbooks that I don't have control of, so can't put code in the this workbook module either - basically I need all the code in a standard module!

Also, I've got another pivot table question, posted here

http://www.mrexcel.com/board2/viewtopic.php?p=1453777#1453777

As you're pivot skills seem top notch, maybe you could have a quick look and see if you can answer that one too!

Thanks again

Charlie

 

[charlie1105]

This is the code that I'm now using, and it works perfectly for me, thanks for your help guys!

Sub DrillDownSpec()
Dim LRSource As Long, LRDest As Long
For Each cell1 In Selection
    cell1.ShowDetail = True
    If cellcounter = 0 Then
        ActiveSheet.Name = "PivotDrillDown"
    Else
        LRSource = ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LRDest = Sheets("PivotDrillDown").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        Range("A2:A" & LRSource).EntireRow.Copy Sheets("PivotDrillDown").Cells(LRDest, 1)
        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Application.DisplayAlerts = True
        Sheets("PivotDrillDown").Select
    End If
cellcounter = cellcounter + 1
Next cell1
End Sub

 

[Tom Urtis]

Wow thanks Tom.
Also, I've got another pivot table question, posted here
http://www.mrexcel.com/board2/viewtopic.php?p=1453777#1453777

I looked at that other post and don't understand your reasons for creating so many pivot tables off the same source data. I have a feeling there is more to your project where that is concerned than the extent of your description. Consider adding to your post a more detailed explanation of why you are doing what you are doing. Pivot tables require a lot of memory and you might be better off creating one pivot table and doing a paste special for values for other reports instead of all those additional pivot tables, unless you have a compelling reason to create so many individual pivot tables.

 

[charlie1105]

Hi Tom

What I'm doing is actually quite a large project with lots of things going on.

It is essentially a series of macros that are run on a set of data, that automate the creating of pivot tables (this will be deployed to a large number of people throughout the firm who have very limited IT skills)

Examples of the macros...

1) creates a pivot table that summaries which day of the week transactions were posted on

2) stratifies the amounts of each invoice (so all invoices <10,000 are in one group, 10,001-20,000 in the next etc)

Each of these add a new column to the source data, which is then used to create the pivot table.

For some tools the user is also given the option to either

1) "break" the code at pivot table, if they want more flexibility - this enables them to then doubleclick (show detail) to get (for example) all transactions on a Sunday, or all transactions in the band 10,001-20,000 etc. or

2) create the drilldown final report (for the weekday example, they could using tickboxes select all transactions on a Saturday or a sunday) and they are left will a summary report. In this case the pivot table is completely destroyed after the showdetail is run.


The end result is a set of reports, each showing a specific summary. Most of what I'm doing is very easy to do manually, and the way I'm writing it isn't how I'd do it myself, but these tools are being written for people who have almost no Excel skills, and need to be able to follow some fairly simple instructions on a userform, and be able to create the desired set of reports.

Hope this helps explain what I'm up to!

 

[Tom Urtis]

Thanks, but a friendly tip, notice I wrote
"Consider adding to your post a more detailed explanation"
which will increase your chances for getting a response on that thread, because all the relevant information about it will be where it belongs there, instead of trying to explain it on this thread here.