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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I get that error if one of the pivot items doesn't exist (eg your nullstring "" or maybe the (blank) or even one of your month values if they aren't all present in the dataset).  I see you have commented out On Error Resume Next - that would be one way of dealing with this.

Do you have an actual date value filed in this database (rather than a text representation of a month)?  If so, you could just Group on this filed by month and year which would sort automatically in proper (date) order.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cecmpbllAuthor Commented:

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!
cecmpbllAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.