Note: The sheet containing your Pivot Table, needs to be the Active Sheet. You can add fields to the newly created Pivot Table called PivotTable1 based on the data range above. Sheets("Sheet2").Select Adding Fields to the Pivot Table TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _ In order to create a Pivot Table based on the data range above, on a new sheet, of the active workbook, we would use the following code: Worksheets("Sheet1").Cells(1, 1).Select TableDestination:="Sheet1!R2C10", TableName:="PivotTable1", DefaultVersion _ "Sheet1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _ In order to create a Pivot Table based on the data range above, on cell J2 on Sheet1 of the Active workbook, we would use the following code: Worksheets("Sheet1").Cells(1, 1).SelectĪ(SourceType:=xlDatabase, SourceData:= _ Name of a column or row field in the table (Optional). Name of a column or row field in the table. GetPivotData ( DataField, Field1, Item1, Field2, Item2 …) where: Parameterĭata field such as sales, quantity etc. In this case, Sales is the “DataField”, “Field1” is Product, “Item1” is ABC, “Field2” is Region and “Item2” is North. The following code will return $980 (the total sales for Product ABC in the North Region) from the Pivot Table: MsgBox ("Sales", "Product", "ABC", "Region", "North") In this case, Sales is the “DataField”, “Field1” is the Region and “Item1” is East. The following code will return $1,130.00 (the total sales for the East Region) from the PivotTable: MsgBox ("Sales", "Region", "East") You can use the PivotTable.GetPivotData method to return values from Pivot Tables. Let’s look at an example: we have a source data set in cells A1:D21 containing the details of products sold, shown below:Īssume you have a PivotTable called PivotTable1 with Sales in the Values/Data Field, Product as the Rows field and Region as the Columns field. Pivot Tables are data summarization tools that you can use to draw key insights and summaries from your data. This tutorial will demonstrate how to work with Pivot Tables using VBA.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |