Using Pivot Table Show Detail to Filter a Recordset

Take any pivot table in the Excel user interface. Double-click any number in the table. Excel inserts a new sheet in the workbook and copies all the source records that represent that number. In the Excel user interface, this is a great way to ad-hoc query a data set.

The equivalent VBA property is called ShowDetail. By setting this property to True for any cell in the pivot table, you will generate a new worksheet with all the records that make up that cell:

PT.TableRange2.Offset(2, 1).Resize(1, 1).ShowDetail = True

The following code produces a pivot table with the total revenue for the top 3 customers and then ShowDetail for each of those customers. This is an alternative method to using the

0 0

Post a comment