my pivot table needs to be sorted by the column "total" . but number of
columns varies, it means the location of the column "total" will be changed
depends on source of data.

can i do that?

dennis

The following code will sort by the grand total column:

'================================
Sub SortPivotTotal()

Dim wsPivot As Worksheet
Set wsPivot = Sheets("Pivot")
Dim rngSort As Range
Dim strSort As String
On Error Resume Next
Set rngSort = wsPivot.Cells.Find(What:="Grand Total", _
After:=wsPivot.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Offset(1, 0)
On Error GoTo 0
If rngSort Is Nothing Then
MsgBox "No Grand Total found"
Exit Sub
Else
strSort = rngSort.Address(ReferenceStyle:=xlR1C1)
rngSort.Sort Key1:=strSort, Order1:=xlDescending, _
Type:=xlSortValues, OrderCustom:=1, _
Orientation:=xlTopToBottom
End If
End Sub
'================================

Dennis Cheung wrote:
my pivot table needs to be sorted by the column "total" . but number of
columns varies, it means the location of the column "total" will be changed
depends on source of data.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

thx, it helps.

i try to use this code to sort the other field other than grand total, but
it doesn't work.

there are 2 fields in column area, they are category and product.
i need the sub-total of each category and sort by the total of products in
each category.
i tried to use the sort function in tools bar, it can sort only the category
which was selected. i needed to sort each category manully after refresh the
table.
can you help?

dennis

"Debra Dalgleish" wrote in message
...
The following code will sort by the grand total column:

'================================
Sub SortPivotTotal()

Dim wsPivot As Worksheet
Set wsPivot = Sheets("Pivot")
Dim rngSort As Range
Dim strSort As String
On Error Resume Next
Set rngSort = wsPivot.Cells.Find(What:="Grand Total", _
After:=wsPivot.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Offset(1, 0)
On Error GoTo 0
If rngSort Is Nothing Then
MsgBox "No Grand Total found"
Exit Sub
Else
strSort = rngSort.Address(ReferenceStyle:=xlR1C1)
rngSort.Sort Key1:=strSort, Order1:=xlDescending, _
Type:=xlSortValues, OrderCustom:=1, _
Orientation:=xlTopToBottom
End If
End Sub
'================================

Dennis Cheung wrote:
my pivot table needs to be sorted by the column "total" . but number of
columns varies, it means the location of the column "total" will be
changed
depends on source of data.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html