Pivot Table

[carddard]

Hi guys,

I need to create a pivot table for all worksheets.
I have dynamic rows in all worksheets.

Here is the code I have.
In the code, ABN refers to worksheet "ABN" only.
However, I need to make this code work for all worksheets with dynamic number of rows.. Anyone can help?

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "ABN!R1C1:R78C17").CreatePivotTable TableDestination:= _
        "'[Sort Heldsec v7.2 with data.xls]ABN'!R80C1", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("DATE"), "Count of DATE", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("DATE")
        .Orientation = xlRowField
        .Position = 1
    End With

Thanks a lot!

 

[Ketan]

Insted of VBA, i would like to suggest to use the Multiple consolidation ranges in pivot wizard. Here you can give the maximum range of sheet you are going to use and generate a pivot table.

check at your end and share your experience.