Macro to create Pivot table with details hidden(Grouped) at first.

**ronanm** · 01-27-2011, 03:46 PM

I have a simple Pivot table which I created with code (A mock up attached)
I need the Pivot table to be created, showing the years as in the example, but with the Detail Rows ("Months") Hidden, until the user double clicks on one of the years so that the underlying hidden rows will show.

Thanks in advance

Ronan

Sub CreatePivot()
'
' CreatePivot Macro
'

'
    Range("B5").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Data!R1C1:R13C3", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet6!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Year")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Amount"), "Sum of Amount", xlSum
   
End Sub

**MarvinP** · 01-27-2011, 04:25 PM

Hi Ronanm,
I think you are looking for this line of code:

   ActiveSheet.PivotTables("PivotTable2").PivotFields("Year").ShowDetail = False

hope that helps

**ronanm** · 01-28-2011, 03:25 PM

Cheers Marvin. Perfect, thanks...Ronan

**ronanm** · 01-29-2011, 12:19 PM

Mmmmnn

However when I use real data I get problems... Code below. I'm guessing this is because I have a couple of levels...

Sub createPivot() 'Added name Range first!
LastRow = Range("A65536").End(xlUp).Row

   Range("A1:W" & LastRow).Select
   Selection.Name = "MyRange"
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "MyRange").CreatePivotTable TableDestination:=Worksheets("Sheet1").Range("A3"), _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
        Sheets("Sheet1").Select
        Range("A3").Select
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
        "Competence Name", "Short Code", "Level", "Data")
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Negative")
        .Orientation = xlDataField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Positive").Orientation = _
        xlDataField
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "By Competence"
  ActiveSheet.PivotTables("PivotTable1").PivotFields("Short code").ShowDetail = False
FormatPivot

End Sub

Thanks

Ronan

**MarvinP** · 01-29-2011, 12:42 PM

Hi Ronan,

Without your workbook, it is a little hard to follow your code. That said here is what to look for.
When you create the pivot table by hand, some options become available OR NOT. If you don't have a second level of rows then Exapand and Collapse don't make sense and won't function. If you have a Blank or non-date in a column of dates that you are trying to group on - you won't see the ability to group by dates. Pivot Tables are kind of smart (I hate accuse a program of being smart) in that it allows or disallows features based on the data or the arrangement of the data.

With all the above. Create your pivot table and see what is available. You are most likely trying to perform a pivot function, in code, that isn't available.

I hope the above helps, but can't be sure without your workbook, code and explaination of what you are trying to accomplish.

**ronanm** · 01-30-2011, 06:22 AM

Hi

Thanks for the reply.

I have attached a screenshot of the Pivot that is created(rather than anonomising the data), and an arrow which points to the Field that hides details when clicked on, plus another arrow which point to another which has already been clicked on.

Really the wish is to have all the fields hidden when the Pivot has been created. Which I think should be OK, as it's not grouping, just hiding...

Thanks

Ronan