aravindhan_31
Well-known Member
- Joined
- Apr 11, 2006
- Messages
- 672
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
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
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....
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
Code:
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 SubIs 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....
