Consulting

Results 1 to 10 of 10

Thread: Creating a detailed pivot from an existing master pivot

  1. #1
    VBAX Regular
    Joined
    Apr 2012
    Posts
    6
    Location

    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.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Can't you just double-click the value pane field and look at the generated table?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Apr 2012
    Posts
    6
    Location
    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

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Should be easily doable. Can you post your workbook?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Apr 2012
    Posts
    6
    Location
    Unfortunately I can't post it due to some sensitive information...what info do you need?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    The data structure.

    Can't you remove 90% of the rows then sterilize the rest?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular
    Joined
    Apr 2012
    Posts
    6
    Location

    File

    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.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Regular
    Joined
    Apr 2012
    Posts
    6
    Location

    Upload

    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.
    Attached Files Attached Files

  10. #10
    VBAX Regular
    Joined
    Apr 2012
    Posts
    6
    Location

    Any luck

    Can anyone shed some light on this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •