Help Needed to write vba for Pivot Table in Excel
Hi,
I need to create a pivot table based on 4 criteria. But the no. of rows containing the data may vary from time to time. I know I can make use of currentregion property to determine the no. of rows but how do I incorporate it into the macro for pivot table?
The code for the pivot table is as follows:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Trade_Participants!C1:R89C4").CreatePivotTabl e TableDestination:="", _
TableName:="PivotTable11", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable11").ColumnGran d = False
ActiveSheet.PivotTables("PivotTable11").AddFields RowFields:="mem_code", _
ColumnFields:=Array("comm_code", "trd_type")
ActiveSheet.PivotTables("PivotTable11").PivotField s("Sum Of match_qty"). _
Orientation = xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Thanks
|