PivotTable Fields and VBA

[Fudging]

Hello,

Currently, I have VBA code that filters data from a dataset and creates PivotTables with a click of a button. It works great but I was informed that the date of the transaction must also be a part of the PivotTable. This seemed easy but, when manually dragging and dropping the "Date" field into the Rows area, it auto adds 2 extra fields, "Months (Date)" and "Days (Date)". When trying to add the "Date" field through VBA though, it only adds "Date" and doesn't auto add the other two.

With that being said, is there a way to add those other 2 fields? Ultimately, I want the pivot table to only contain "Months (Date)", meaning the data is separated by the month, this means that I need to either hide or delete the "Date" & "Days (Date)" fields.

If it helps, the date is formatted as a short date and the VBA code I used to add the Date col to the pivot table is ".PivotFields("Date").Orientation = xlRowField".
I also tried adding .PivotFields("Months (Date)")... but I got errors saying it couldn't find the field.

Thanks for the help!

 

[Domenic]

If you record a maco while manually creating your PivotTable, you'll see that it uses the AutoGroup method of the PivotField object to group the dates by month and day. Then it sets the orientatioin for the "Days (Date)" PivotField to xlHidden to hide it, for example...

    With pt.PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
        .AutoGroup
    End With
    
    pt.PivotFields("Days (Date)").Orientation = xlHidden
    
    
    pt.PivotFields("Date").Orientation = xlHidden

...where pt represents a PivotTable.

Hope this helps!

 

[Fudging]

If you record a maco while manually creating your PivotTable, you'll see that it uses the AutoGroup method of the PivotField object to group the dates by month and day. Then it sets the orientatioin for the "Days (Date)" PivotField to xlHidden to hide it, for example...

    With pt.PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
        .AutoGroup
    End With
   
    pt.PivotFields("Days (Date)").Orientation = xlHidden
   
   
    pt.PivotFields("Date").Orientation = xlHidden

...where pt represents a PivotTable.

Hope this helps!

Sweet, it works!

Thanks for the help, I greatly appreciate it

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!