PivotTable Fields and VBA

Fudging

New Member
Joined
Aug 26, 2022
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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...

VBA Code:
    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!
 
Upvote 0
Solution
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...

VBA Code:
    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 :)
 
Upvote 0

Forum statistics

Threads
1,224,567
Messages
6,179,571
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top