Create pivot automatically in all the sheets based on the data in sheets

[aravindhan_31]

Hi,

I have about 20 sheets in a workbook. I need to create pivot for all the data in each sheets. the Data, Column & Rows in pivot are same ( table contents are same for all the pivots) contents in table are different in each sheets. one sheet may have 2 rows, another sheet may have 35 rows, etc,

I have recored a macro for one sheet, do not know how to apply the same in all the sheets.

the code is

Sub pivot()
'
' pivot Macro
' Macro recorded 9/29/2008 by ml71
'
    Range("A1").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Dept T'!R1C1:R3C85").CreatePivotTable TableDestination:= _
        "'[pivot test.xls]Dept T'!R12C1", TableName:="PivotTable3", DefaultVersion _
        :=xlPivotTableVersion10
    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="Supplier_Number" _
        , ColumnFields:="Item"
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Total_Retail").Orientation _
        = xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    Range("E21").Select
End Sub

Is there a way that we run a macro, the pivot is automatically created in all the sheets, and the pivot table should appear in the existing sheet just after the data.

Arvind....

 

[aravindhan_31]

Here is the cross post:
http://www.excelforum.com/excel-pro...-based-on-the-data-in-sheets.html#post1974264

 

[Fazza]

Hi, Arvind.

This is similar http://www.mrexcel.com/forum/showthread.php?t=315768

HTH, Fazza