Software

Ask something
Question Locked

Excel 2003 Macro problem with PivotTables

0 helpful
2 replies

by o0x0o

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

I have a working macro to create a PivotTable from a large data set, but what I then need to do, is sort the data in descending order of 'Grand Total', and show only the top 10.

I know how to do this manually, and have tried to use the macro recorder, but the code it provides never works... It looks like it's trying to use the .AutoShow module like this:

.AutoShow xlAutomatic, xlTop, 10, "Count of Ci - Asset"
.AutoSort xlDescending, "Count of Ci - Asset"

But I get errors when attempting to use.

Will post my full code in a reply to make it easier to read.

Thanks for any help,
Tony

2 replies

by o0x0o

Sub CreateCIPivot()
Dim WSD As Worksheet
Dim WSDout As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("Scratch Sheet")
Set WSDout = Worksheets("test")

' Delete any prior pivot tables
For Each PT In WSDout.PivotTables
PT.TableRange2.Clear
Next PT

' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSDout.Cells(23, 2), TableName:="")

' Turn off auto-updating while the table is building
PT.ManualUpdate = True

' Set up the row & column fields
PT.AddFields RowFields:=Array("Ci - Asset"), ColumnFields:="Month"

' Set up the data fields
With PT.PivotFields("Ci - Asset")
.Orientation = xlDataField
'.AutoShow xlAutomatic, xlTop, 10, "Count of Ci - Asset"
'.AutoSort xlDescending, "Count of Ci - Asset"
End With

' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True

End Sub

This discussion is closed to new replies.