Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Pivottable
I am trying to sort my data first and then refresh pivot tables using the
following macro, Private Sub Worksheet_Change(ByVal Target As Range) Range("Data1").Sort Key1:=Range("P18"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=10, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWorkbook.RefreshAll End Sub The problem is that the pivottables are not refreshing correctly. Is the reason due to the fact that the data being sorted has not completed when the pivot starts to refresh? |
#2
|
|||
|
|||
I don't think so.
But I don't recall when just changing the sort order of my raw data would make the pivottable even change (after it was refreshed). One of the nice features of pivottables is that the raw data can be in any order. nc wrote: I am trying to sort my data first and then refresh pivot tables using the following macro, Private Sub Worksheet_Change(ByVal Target As Range) Range("Data1").Sort Key1:=Range("P18"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=10, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWorkbook.RefreshAll End Sub The problem is that the pivottables are not refreshing correctly. Is the reason due to the fact that the data being sorted has not completed when the pivot starts to refresh? -- Dave Peterson |
#3
|
|||
|
|||
Dave
Thanks for your reply. My data has an extra field containing a vlookup function, it's result changes based on the order of the data. Another point, the pivottable refreshes correctly when I click the pivot refresh button manually or run a seperate macro with the refreshall keyword. "Dave Peterson" wrote: I don't think so. But I don't recall when just changing the sort order of my raw data would make the pivottable even change (after it was refreshed). One of the nice features of pivottables is that the raw data can be in any order. nc wrote: I am trying to sort my data first and then refresh pivot tables using the following macro, Private Sub Worksheet_Change(ByVal Target As Range) Range("Data1").Sort Key1:=Range("P18"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=10, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWorkbook.RefreshAll End Sub The problem is that the pivottables are not refreshing correctly. Is the reason due to the fact that the data being sorted has not completed when the pivot starts to refresh? -- Dave Peterson |
#4
|
|||
|
|||
Maybe adding
application.calculate before the refreshall would help. nc wrote: Dave Thanks for your reply. My data has an extra field containing a vlookup function, it's result changes based on the order of the data. Another point, the pivottable refreshes correctly when I click the pivot refresh button manually or run a seperate macro with the refreshall keyword. "Dave Peterson" wrote: I don't think so. But I don't recall when just changing the sort order of my raw data would make the pivottable even change (after it was refreshed). One of the nice features of pivottables is that the raw data can be in any order. nc wrote: I am trying to sort my data first and then refresh pivot tables using the following macro, Private Sub Worksheet_Change(ByVal Target As Range) Range("Data1").Sort Key1:=Range("P18"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=10, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWorkbook.RefreshAll End Sub The problem is that the pivottables are not refreshing correctly. Is the reason due to the fact that the data being sorted has not completed when the pivot starts to refresh? -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
Dave
Thanks a lot. This solved the problem. "Dave Peterson" wrote: Maybe adding application.calculate before the refreshall would help. nc wrote: Dave Thanks for your reply. My data has an extra field containing a vlookup function, it's result changes based on the order of the data. Another point, the pivottable refreshes correctly when I click the pivot refresh button manually or run a seperate macro with the refreshall keyword. "Dave Peterson" wrote: I don't think so. But I don't recall when just changing the sort order of my raw data would make the pivottable even change (after it was refreshed). One of the nice features of pivottables is that the raw data can be in any order. nc wrote: I am trying to sort my data first and then refresh pivot tables using the following macro, Private Sub Worksheet_Change(ByVal Target As Range) Range("Data1").Sort Key1:=Range("P18"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=10, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveWorkbook.RefreshAll End Sub The problem is that the pivottables are not refreshing correctly. Is the reason due to the fact that the data being sorted has not completed when the pivot starts to refresh? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivottable data field | Excel Discussion (Misc queries) | |||
Sorting in PivotTable | Excel Discussion (Misc queries) | |||
Pivottable Dates | Excel Discussion (Misc queries) | |||
PivotTable canned functions | Excel Discussion (Misc queries) | |||
How do I set permanent secondary axis in PivotTable chart? | Charts and Charting in Excel |