In Excel 2000 and above, before creating a pivot table you need to create a pivot cache to define the data source. The best part is, you don’t need to change data source every time while creating the pivot table. This code will start from the first cell of the data table and select up to the last row and then up to the last column.Īnd finally, define that selected range as a source. Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row And below is the code: 'Define Data Range You need a code which can identify the entire data from source sheet. Here you need to take care of one thing that you can’t specify a fixed source range. Now, next thing is to define data range from the source worksheet. 'Declare VariablesĬustomization Tip: If the name of the worksheets which you want to refer in the code is different then make sure to change it from the code where I have highlighted. It will insert a new worksheet with the name “Pivot Table” before the active worksheet and if there is worksheet with the same name already, it will delete it first.Īfter inserting a new worksheet, this code will set the value of PSheet variable to pivot table worksheet and DSheet to source data worksheet. LastRow and LastCol: To get the last row and column of our data range.īefore creating a pivot table, Excel inserts a blank sheet and then create a new pivot table there.Īnd, below code will do the same for you.PTable: To use as a name for our pivot table.PChache: To use as a name for pivot table cache.PSheet: To create a sheet for a new pivot table. The first step is to declare the variables which we need to use in our code to define different things. Make sure to download this file from here to follow along. After following these steps you will able to automate your all the pivot tables. The Simple 8 Steps to Write a Macro Code in VBA to Create a Pivot Table in Excelįor your convenience, I have split the entire process into 8 simple steps. Without any further ado, let’s get started to write our macro code to create a pivot table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |