Use VBA in Excel to Create and Repair Pivottable: Pivotfields

Use VBA in Excel to Create and Repair Pivottable: Pivotfields covers the essential facts behind the topic, including what changed, why it mattered, and how it affected users.

Pivotfields Overview

Before creating the PivotTable like Figure 2, I selected Record New Macro. As shown in Figure 3, to see how the code is recorded.

Figure 3

Then I go to VBE screen by pressing Alt + F11. I entered Module1, seeing the following code: Sub Macro1 () Macro1 Macro1 Macro recorded 17/03/2003 by Duyet Range ("A1: D13"). Select ActiveWorkbook. PivotCaches. Add (SourceType: = xlDatabase, SourceData: = _ "Sheet1! R1C1: R13C4"). CreatePivotTable TableDestination: = Range ("A1"), _ TableName: = "PivotTable1"

ActiveSheet. PivotTables ("PivotTable1"). SmallGrid = False

Note:

Region

A page field in a PivotTable.

SalesRep

Is the row field in the PivotTable.

Month

The column field in the PivotTable.

Sales

A data field in a PivotTable using the Sum function

ActiveSheet. PivotTables ("PivotTable1"). AddFields RowFields: = "SalesRep", _ ColumnFields: = "Month", PageFields: = "Region" ActiveSheet. PivotTables ("PivotTable1"). PivotFields ("Sales"). Orientation = _ xlDataField End Sub

Examine the code that has been written: To investigate the code above you need to know some related objects. All of these objects are explained on online help.

PivotCaches

Is a collection of PivotCache objects in the Workbook object

PivotTables

Is a collection of PivotTable objects in the Workbook object

PivotTableFields

Is a collection of fields in a PivotTable object

Create PivotTable

A method of PivotCache object to create a PivotTable using data in a PivotCache

We can rewrite the above procedure using the CreatePivotTable procedure (note that you enter this procedure into module1), it may be a bit long but will be easier to understand, and you can run the program anywhere by pressing Alt + F8 key combination, then select the CreatePivotTable procedure and select Run as shown in Figure 4.

Sub CreatePivotTable () Dim PTCache As PivotCache Dim PT As PivotTable Application. ScreenUpdating = False Rub the PivotSheet at the ear

On Error Resume Next Application.DisplayAlerts = False Sheets ("PivotSheet") Delete On Error GoTo 0 Tao Pivot Cache Set PTCache = ActiveWorkbook.PivotCaches.Add _ (SourceType: = xlDatabase, _ SourceData: = Sheets ("Sheet1"). Range ("A1"). CurrentRegion.Address) Create new worksheet and catalog Worksheets.Add ActiveSheet.Name = "PivotSheet" Tao Pivot Table tu Cache Set PT = PTCache.CreatePivotTable _ (TableDestination: = Sheets ("PivotSheet"). Range ("A1"), _ TableName: = "PivotTable1") With PT Them the schools .PivotFields ("Region") Orientation = xlPageField .PivotFields ("Month"). Orientation = xlColumnField .PivotFields ("SalesRep") Orientation = xlRowField .PivotFields ("Sales") Orientation = xlRowField Application.ScreenUpdating = True End With End Sub When you finish the above procedure, you will get a PivotTable in sheet2, in this case the sheet is called PivotSheet. ( Figure 5 ) If you pay attention, you will see the difference of the two code above. In Macro1 when using the Add method to create PivotCache , SourceData is " Sheet1! R1C1: R13C4 " and in the code I write Sheets ("Sheet1"). Range ("A1"). CurrentRegion.Address . Here I use the Current Region property , which means that the data we use is based on the current region around cell A1. This is to ensure that the CreatePivotTable procedure continues to work well when we add data. Now suppose I have added the Target field in the data block, and in the PivotTable I will add the target field and also add the Variance calculation field. This field (Variance) will equal Sales - Target . My new data block is shown in Figure 6 .

The code in the CreatePivotTable above procedure will be added as follows (I just added in the With PT. End With paragraph): With PT Them the schools

.PivotFields ("Region") Orientation = xlPageField .PivotFields ("Month"). Orientation = xlColumnField .PivotFields ("SalesRep") Orientation = xlRowField .PivotFields ("Sales") Orientation = xlDataField .PivotFields ("Target") Orientation = xlDataField Great news .CalculatedFields.Add "Variance", "= Sales - Target" .PivotFields ("Variance"). Orientation = xlDataField Replace doi caption .PivotFields ("Sum of Sales"). Caption = "Sales ($)" .PivotFields ("Sum of Target"). Caption = "Target ($)" .PivotFields ("Sum of Variance"). Caption = "Variance ($)" End With After re-running the above procedure I will be like Figure 7 . Suppose my data now consists of 6 months ( Figure 8 ), I want to add the total column by 3 months. I have to correct my code as follows: With PT Them the schools .PivotFields ("Region") Orientation = xlPageField .PivotFields ("Month"). Orientation = xlColumnField

.PivotFields ("SalesRep") Orientation = xlRowField .PivotFields ("Sales") Orientation = xlDataField .PivotFields ("Target") Orientation = xlDataField Great news .CalculatedFields.Add "Variance", "= Sales - Target" .PivotFields ("Variance"). Orientation = xlDataField Full account .PivotFields ("Month"). CalculatedItems.Add "Q1", _ "= ladder 1 + ladder 2 + ladder 3" .PivotFields ("Month"). CalculatedItems.Add "Q2", _ "= ladder 4 + ladder 5 + ladder 6" Move the files .PivotFields ("Month"). PivotItems ("Q1"). Position = 4 .PivotFields ("Month"). PivotItems ("Q2"). Position = 8 Replace doi caption .PivotFields ("Sum of Sales"). Caption = "Sales ($)" .PivotFields ("Sum of Target"). Caption = "Target ($)" .PivotFields ("Sum of Variance"). Caption = "Variance ($)" End With After re-running the CreatePivotTable procedure I will get the result as shown in Figure 9 .

Well, come here, you see, if we know how to use VBA, data analysis will become simpler. In addition, we can also create a PivotTable from an external data source such as Access. For good PivotTable programming, you should read Excel's online help section on the objects, methods, and attributes mentioned above. Hopefully the above article will help you in part. Any suggestions, please send to levanduyet@pmail. Vnn. Vn. Le Van Duyet