sort in pivot table

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" <dsd@contexturesXSPAM.com> wrote in message
news:426A25AB.5000700@contexturesXSPAM.com...
> 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
>