write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Thursday, 29 December 2011

Excel VBA - Pivot Table

You can record the steps of creating pivot table & generate pivot table
if you data range changes than you can use dynamic Range to create pivot table
Main Pivot table elements
PivotCaches :-
You can not see this.The PivotCache is a container that holds a static copy of the source data in memory
If you were to create a pivot table in VBA without using the wizard you'd need to address the pivot cache issue in code, like the following codeline which, if executed in the Immediate Window when the source data's sheet is active, would create a basic pivot table on that sheet on row 3, 2 columns to the right of the last used column (assuming the source data headers start in A1).

ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion).CreatePivotTable TableDestination:="R3C" & Range("A1").CurrentRegion.Columns.Count + 2

Normal Range : "Sheet1!R1C1:R7C6"
Dynamic Range : =OFFSET('Source Data'!$A$1,0,0,COUNTA('Sheet1'!$A:$A),6)

TableDestination : 
"Sheet1!R2C11" OR
ActiveSheet.Cells(3, 1)

along with this there are DefaultVersion,RowFields,PivotFields().Orientation

'To create Pivot table
Sub create_pivot()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="'Pivot Table & VBA'!data").CreatePivotTable _
TableDestination:=ActiveSheet.Cells(10, 10), _
TableName:="PivotTable1", _
ActiveSheet.PivotTables(1).AddFields RowFields:="Name"
ActiveSheet.PivotTables(1).PivotFields("Name").Orientation = xlDataField
End Sub

'To delete all pivot tables
Sub Delete_All_Pivot_Tables_In_Sheet()
For Each pvt In ActiveSheet.PivotTables
    pvt.PivotSelect "", xlDataAndLabel, True
Next pvt
End Sub

No comments:

Post a Comment