• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 755
  • Last Modified:

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

0
cecmpbll
Asked:
cecmpbll
  • 2
1 Solution
 
RichardSchollarCommented:
Hi

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.

Richard
0
 
cecmpbllAuthor Commented:
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!
0
 
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now