Refresh Pivot and Sort By Data Source Order
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:
Code:
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?
Re: Refresh Pivot and Sort By Data Source Order
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.
Re: Refresh Pivot and Sort By Data Source Order
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
Code:
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