Creating a detailed pivot from an existing master pivot

Disclaimer: I'm very much a novice to VBA.

I'm trying to take a pivot table that I have made and create a macro that you can use to highlight any cell in the pivot table so that it will open the detail and then create a new more detailed pivot on that detail only.

Essentially I have about 5000 data points that fit into all of these different categories, too many to list on one pivot and have it be useful, but it is useful to drill into one particular data set of say 30 points and make a very detailed pivot from that data. If anyone can guide me as to how to create a pivot table on a new sheet from the original pivot table, I'd appreicate it.:beerchug:

Can't you just double-click the value pane field and look at the generated table?

Yea but what I want to do is then take that detail table and create another pivot table from that with specific row/column layouts different from the master table. Bascially I want to be able to highlight any cell on the master pivot, click a macro, and have it create a new tab that hasthis more detailed pivot table on it, for any data point.

This way I don't have to actually build the pivot table everytime since there are hundreds of location/department combinations that I want to look into in detail.

Thanks


Here's my attempt, very lengthy but it's mostly formatting, I can't figure out how to generalize it so that a new sheet/pivot are made everytime I highlight a cell and run the macro

Sub Macro2()
'
' Macro2 Macro
'
'
Range("I11").Select
Selection.ShowDetail = True
Cells.Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet5!R1C1:R1048576C24", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Sheet6!R3C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Sheet6").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Segment")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("OBU Description")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Dept Desc")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Supervisor Name")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Last Name")
.Orientation = xlRowField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("First Name")
.Orientation = xlRowField
.Position = 6
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Title")
.Orientation = xlRowField
.Position = 7
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Emplid"), "Count of Emplid", xlCount
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("FTE"), "Count of FTE", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of FTE")
.Caption = "Sum of FTE"
.Function = xlSum
End With
Range("A5").Select
With ActiveSheet.PivotTables("PivotTable2")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit
Range("E4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Last Name").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("D4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Supervisor Name"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Range("C4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Dept Desc").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("B4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("OBU Description"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
Range("A4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Segment").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
Range("F4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("First Name").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
Range("G4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Title").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveWindow.SmallScroll Down:=-6
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Segment")
.PivotItems("(blank)").Visible = False
End With
ActiveWorkbook.Save

Should be easily doable. Can you post your workbook?

Unfortunately I can't post it due to some sensitive information...what info do you need?

The data structure.

Can't you remove 90% of the rows then sterilize the rest?

I'm going to purge data and desensitize it tomorrow do you have an email address I can send the cleaned file to? Or is there an easy way to post it on here? Thanks again I really appreciate the help.

Better to post it here, more people who could respond then. In the reply pane, click the 'Go Advanced' button and in the new dialog there is a'Manage Attachments' button.

Here's the cleaned file, yellow tabs are the ones I want to creat automatically from the master pivot with a macro just by highlighting any cell in the master pivot tab and running the macro.

Can anyone shed some light on this?