Pivottable


G

Guest

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?
 
Ad

Advertisements

D

Dave Peterson

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.
 
G

Guest

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.
 
D

Dave Peterson

Maybe adding

application.calculate

before the refreshall would help.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivottable 0
PivotTable 1
PivotTable 1
PivotTable 1
PivotTables 4
Pivottable 0
PivotTable 2
PivotTable 0

Top