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

Try this SourceData

SourceData:=Sheets("Trade_Participants").Range("A1 ").CurrentRegion

-vemaju