Help with custom pivot table sorting

clawlan

Board Regular
Joined
Aug 9, 2010
Messages
62
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>

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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

mohan_2mm

Board Regular
Joined
Apr 22, 2010
Messages
75
You can change the data source every time instead of creating new pivot eveytime.

For existing pivot you can change the data source

Options - Under data tab - change source - select the desired source.
 

clawlan

Board Regular
Joined
Aug 9, 2010
Messages
62
You can change the data source every time instead of creating new pivot eveytime.

For existing pivot you can change the data source

Options - Under data tab - change source - select the desired source.
This is for upper management, so it has to be totally automated.
 

clawlan

Board Regular
Joined
Aug 9, 2010
Messages
62
This is for upper management, so it has to be totally automated.
The macro for adjusting the data range is

Code:
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "PO!R1C1:R100C28" _
        , Version:=xlPivotTableVersion10)
With "R100C28" representing the rows/columns. If there was a way I could calculate the new range and insert it as this format, it could work. Unfortunately, I have no idea how to do that. Ideas?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,451
Messages
5,511,437
Members
408,848
Latest member
gNat

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top