Excel Drilldown in Pivot Tables

I've created a pivot table and have enabled drill down on it. However, on drill down - i.e. double-clicking a row on the pivot table, it creates a new sheet. I want to be able to go back to the original sheet. Is this possible without writing a macro?

Thanks
Sonali

Hi Sonali,

You wouldn't be able to do this without writing a macro. If you just want to return to the data source when the user double clicks then there is no problem. Try this:-

Disable drill down, then right click your worksheet tab and choose View Code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Sheets("Data source").Activate
End Sub

That would take you to your data source. If you want to return to the actual records then it would be far more elaborate. You'd have to determine the row and column header where the user has double clicked and then search the data source for the correct records. Unless you only have a few pivot fields then this could be a difficult task.

Regards,
D

Drilldown will not highlight the data in the
original source data list; however, you could use
the separate list created by drilldown as a
critiera and filter the original data source with
an Advanced AutoFilter.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

Drilldown in Pivot Tables

Re: Drilldown in Pivot Tables

No, if I understand your intentions...