Refresh Pivot and Sort By Data Source Order

[kadzu]

I have many differents Worksheets with 1 or more pivot tables, their source is always a worksheet.

For example, the source of a PivotTable is "sheet_data1!$A$1:$I$1149". The worksheet "sheet_data1" has 1149 rows. I have a program that changed this worksheet and it could increase or decrease the number of rows in that sheet.

So the pivot will not calculate certain rows if there are more rows tha before or consider empty rows when there are less rows than before.

This is the code to change the source:

Private Sub Workbook_Open()
    For Each St In ActiveWorkbook.Worksheets
        For Each pt In St.PivotTables
                Dim StartPoint As Range
                Dim NewRange As String
                Dim LastCol As Long
                Dim lastRow As Long
                Dim Data_Sheet As Worksheet
                Dim DataRange As Range

                Set Data_Sheet = ThisWorkbook.Worksheets(Split(pt.PivotCache.SourceData, "!")(0))
                Set StartPoint = Data_Sheet.Range("A1")
                LastCol = StartPoint.End(xlToRight).Column
                DownCell = StartPoint.End(xlDown).Row
                
                Set DataRange = Data_Sheet.Range(StartPoint, Data_Sheet.Cells(DownCell, LastCol))
                NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

                pt.ChangePivotCache ActiveWorkbook. _
                PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
        Next
    Next
End Sub

Now the problem is that I need the items to be in the same order of the data source but when I change the data source with the code above, it keeps the order of the old table while placing the new values at the end.

For example, I have a field called month(it's a string and it would make other things more complicated if changed to another type). In the previous data source it was in the same order as the data source :

May/2017
Jun/2017
Jul/2017
Aug/2017
Sep/2017
Oct/2017
Nov/2017
Dec/2017
Jan/2018
Feb/2018
Mar/2018
Apr/2018
May/2018

Now the data source added 4 more months but the pivot table is not in the same order as the data source.

Pivot after data source was changed and refreshed:
May/2017
Jun/2017
Jul/2017
Aug/2017
Sep/2017
Oct/2017
Nov/2017
Dec/2017
Jan/2018
Feb/2018
Mar/2018
Apr/2018
May/2018
Apr/2017
Mar/2017
Feb/2017
Jan/2017

New Data Source Order
Jan/2017
Feb/2017
Mar/2017
Apr/2017
May/2017
Jun/2017
Jul/2017
Aug/2017
Sep/2017
Oct/2017
Nov/2017
Dec/2017
Jan/2018
Feb/2018
Mar/2018
Apr/2018
May/2018

I heard this is a problem with the pivots cache but didnt find a way to solve. Anyone knows how to solve this?