asked on

Manually setting pivotitem sort order to put months in order

I have a pivot table that refreshes with code that directly modifies the CommandText SQL code to obtain data from a database. I then place the Year and Month fields into the pivot table. However the months are not placed in the correct order. I have attempted to manually define where they should be with the attached code.

I get an "Unable to get the PivotItems property of the PivotField class" error. Is there anything wrong with my code?

With ActiveSheet.PivotTables(Pname).PivotFields("CAL_YEAR")
    .Orientation = xlColumnField
    .Position = 1
    .Subtotals(1) = True
    .Subtotals(1) = False
    On Error Resume Next
    .PivotItems("").Visible = False
    .PivotItems("(blank)").Visible = False
    On Error GoTo 0
End With


With ActiveSheet.PivotTables(Pname).PivotFields("MTH_NAME")
    .Orientation = xlColumnField
    .Position = 2
    .Subtotals(1) = True
    .Subtotals(1) = False
    'On Error Resume Next
    .PivotItems("").Visible = False
    .PivotItems("(blank)").Visible = False
    .PivotItems("Jan").Position = 1
    .PivotItems(“Feb”).Position = 2
    .PivotItems(“Mar”).Position = 3
    .PivotItems(“Apr”).Position = 4
    .PivotItems(“May”).Position = 5
    .PivotItems(“Jun”).Position = 6
    .PivotItems(“Jul”).Position = 7
    .PivotItems(“Aug”).Position = 8
    .PivotItems(“Sep”).Position = 9
    .PivotItems(“Oct”).Position = 10
    .PivotItems(“Nov”).Position = 11
    .PivotItems(“Dec”).Position = 12
    
    'On Error GoTo 0
End With

Open in new window

ASKER CERTIFIED SOLUTION

RichardSchollar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cecmpbll

ASKER

Richard,

Thanks so much for your quick reply, this has completely solved the issue. I do indeed have the actual date value for each record, but I was unaware of the grouping options available for PTs. Since the PT is only refreshed and never removed, I grouped the date field by month and year, and the VBA code simply adds those fields when necessary. Many thanks!

cecmpbll

ASKER

Some more description of the solution offered would have been good, but it was easy enough to find the answer online once I knew that such a thing was possible. Thanks for the help!