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?

[Ecniv]

Set all sources to the max rows (50000?) and set the option of blank to ignore/do not display.

otherwise... on an initialise or open event or button refresh event youd need to loop all the pivots get the source, return to the top left, and re-make the range selected to the new range... then update the pivot.
this would require the titles to be complete with no breaks and a column that is guarenteed to have data in always, so you can move around the sheet quickly.

Sorting should be able to be applied to the pivot tables (if i recall correctly). If you change the source I think it forces them to refresh anyway.. just lose any customizations on the pivots if manually applied.

[kadzu]

I think I solved(still looking for possible troubles caused by this solution but none found so far) :

1. I created one empty version of each sheet thats used as a source(with the name of the original + "_empty")
2. I changed the source of each sheet to their equivalent emptysheet. The empty sheets need to have the same header and at least one row with data with columns in the same type as the original but different values(they cant be have the same value of anything in the original) so just put some random values that you are sure to never show up in the original.
3. I cleared the missing values in the cache with "PivotCache.MissingItemsLimit = xlMissingItemsNone"
4. I change source to the original sheets
5. I cleared the missing values in the cache with "PivotCache.MissingItemsLimit = xlMissingItemsNone" to remove trash from emptysheet

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 EmptyRange 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))
            EmptyRange = Data_Sheet.Name & "_empty!" & DataRange.Address(ReferenceStyle:=xlR1C1)

            pt.ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:=EmptyRange)
            
            pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
            pt.PivotCache.Refresh
            
            NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)
            pt.ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

            pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
            pt.PivotCache.Refresh
        Next
    Next
End Sub