I have a pivot table in which "PO or Plan" is one of the row items. Currently, due to Alphabetical order, cells that say "Planned Order" are first, with "PO11123..." following in chronological order. I want the "Planned Order" items at the bottom of the list. In the excel GUI, I can highlight the cell, drag is to the bottom of the list, and the rest follow, but I can't figure out how to do it in the code.
Recording a macro shows it being given a static position, which won't work for me since the pivot table source data changes daily. I also can't do reverse alphabetical because then the PO numbers are backwards. Any ideas?
Current:
<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="116" height="144"><col style="width: 68pt;" width="90"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 68pt;" width="90" height="17">Planned Order</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO116560</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO116810</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO116854</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117061</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117255</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117427</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117657
</td> </tr> </tbody></table>
What I want:
<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="115" height="162"><col style="width: 68pt;" width="90"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 68pt;" width="90" height="17">PO116560</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO116810</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO116854</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117061</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117255</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117427</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117657</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">Planned Order</td> </tr> </tbody></table>
Recording a macro shows it being given a static position, which won't work for me since the pivot table source data changes daily. I also can't do reverse alphabetical because then the PO numbers are backwards. Any ideas?
Current:
<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="116" height="144"><col style="width: 68pt;" width="90"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 68pt;" width="90" height="17">Planned Order</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO116560</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO116810</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO116854</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117061</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117255</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117427</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117657
</td> </tr> </tbody></table>
What I want:
<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="115" height="162"><col style="width: 68pt;" width="90"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 68pt;" width="90" height="17">PO116560</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO116810</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO116854</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117061</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117255</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117427</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">PO117657</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" height="17">Planned Order</td> </tr> </tbody></table>
Code:
Sub CreatePivotTable()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("PO")
Dim WSR As Worksheet
' Delete old pivot table worksheet and create a new one
Application.DisplayAlerts = False
Sheets("OpenQty Table").Delete
Application.DisplayAlerts = True
Sheets.Add.Name = "OpenQty Table"
ActiveSheet.move after:=Worksheets(Worksheets.Count)
Sheets("PO").Select
' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=Worksheets("OpenQty Table").Range("A1"), TableName:="PivotTable1")
' Turn off updating while building the table
PT.ManualUpdate = True
' Set up the row and column fields
PT.AddFields RowFields:=Array("Prefered Supplier", "M-Spec", "PO or Plan"), ColumnFields:="DueDate"
' Set up the data field
With PT.PivotFields("OpenQty")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
' Ensure that you get zeroes instead of blanks in the data area
PT.NullString = "0"
' Calc the pivot table to allow the date label to be drawn
PT.ManualUpdate = False
PT.ManualUpdate = True
'Focus on pivot table worksheet to allow DueDate grouping by month
Sheets("OpenQty Table").Select
' Group DueDate by month, year
PT.PivotFields("DueDate").LabelRange.Group _
Start:=Now, End:=True, Periods:=Array(False, False, False, _
False, True, False, True)
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
End Sub