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