Pvsheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" Pvsheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True Row Axis Layout is also set in the way you want. This will specify the type of table through table style. And the pivot table is almost finished now you can set the format of the pivot table. Step 17: You have inserted the fields that need to create a pivot table. Now we need to insert the number of sales to the pivot table.Ĭode: 'Insert Sales column to the data field With pvsheet.PivotTables("Sales_Report").PivotFields("Sales") Step 16: Till this, the data inserted were text type.
#Learn how to use pivot tables in excel code
Give the code to insert the town field.Ĭode: 'Insert town to Column Filed With pvsheet.PivotTables("Sales_Report").PivotFields("Town") Step 15: Insert the next field to the pivot table, and the next is a town. In the same way, insert the second-row field street to the pivot table.Ĭode: 'Insert Street to Row Filed & position 2 With pvsheet.PivotTables("Sales_Report").PivotFields("Street") Step 14: The next is to specify the second field you want to insert to the pivot table. Here the first row starts with the product.Ĭode: 'Insert Product to Row Filed With pvsheet.PivotTables("Sales_Report").PivotFields("Product")
Step 13: Once the pivot is inserted, you have to specify the different fields you want to insert to the pivot table. Create a pivot table as blank to which you can add the data set further.Ĭode: 'new blank pivot table Set pvtable = pvcache.CreatePivotTable(TableDestination:=pvsheet.Cells(1, 1), TableName:="Sales_Report") Step 12: Here, the Sales report for different products will be converted into a pivot table through this. Use the object pvcache to set the source cache.Ĭode: 'pivot cahe Set pvcache = (xlDatabase, SourceData:=pvrange) Step 11: It’s time to set the pivot cache, which is the pivot table’s source. So the pvrange will adjust is there any addition or deletion of the rows or column happen.
.jpg "learn how to use pivot tables in excel")
Since the range is set using resize property of cell ranges, it will resize the pvrange will adjust the pivot range accordingly. It is already declared as a variable to use the pivot range “pvrange”.Ĭode: 'initializing pivot table data range Set pvrange = pdsheet.Cells(1, 1).Resize(plr, plc) You have to set the pivot range in the next step. Step 10: Like we mentioned previously, the range of cells specifies the data in the worksheet. Plc = pdsheet.Cells(1, Columns.Count).End(xlToLeft).Column Plr = pdsheet.Cells(Rows.Count, 1).End(xlUp).Row
Find the last used row and column using the declared variables plr and plc.Ĭode: 'two variable to find Last used row and column in pdsheet Step 9: Once the worksheets are set, the next item is we need the last used row and column for creating a pivot report. This will be used further for specifying the worksheets.Ĭode: Set pvsheet = Worksheets("pvsheet") Step 8: Assign an object variable for the pivoting sheet and data sheet respectively to the variables pvsheet and pdsheet. Worksheets.Add After:=ActiveSheet ' to add a new worksheetĪctiveSheet.Name = "pvsheet" ' to rename the worksheet into "pvsheet" On Error GoTo 0 Worksheets("pvsheet").Delete 'to delete the exisiting pivot table in the worksheet To delete the previous pivot table sheet and create a new sheet to insert the pivot table. This will help to avoid confusions on which table the source data to be plotted. Step 7: The next step is to delete if there is any pivot table already created. This can be any row or column, so there are chances to be a number of rows and columns beyond the integer data type limit. Step 6: You need two more variables as long datatype to indicate the last used row and column for pivot table creation.
So the datasheet variable is declared as pdsheet. Step 5: You need a similar variable to use the datasheet, which should contain the data you want to plot as a pivot table. Step 4: A worksheet needs to insert the pivot table which you need to create. In the pivot table, the source data is spread as rows and columns so to point to a particular range we need a variable. Step 3: The data is within the worksheet and should specify a range to hit the cell that you need. Declare the variable pvcache for pivot cache. Before creating a pivot table, declare a pivot cash variable. Step 2: To keep the data files, we need a pivot cache to declare the source of the data. Declare a function and pivot table object. Create an object for the pivot table this object will be used to point to the pivot table on further codes. Step1: Pivot table should consider as a reference object.
#Learn how to use pivot tables in excel download
You can download this VBA Pivot Table Excel Template here – VBA Pivot Table Excel Template
0 kommentar(er)
