Go Back

How to Create a Pivot Table with VBA in Excel

We can use VBA to create a pivot table in less time by applying macro codes. In this guide, we will learn how to automate our pivot table using VBA.

Figure 1- How to create a Pivot table with VBA

Convert data to a table

  • We will convert our data to a table by clicking Insert, and then, Table

Figure 2- Click on Table

Figure 3- Create Table dialog box

  • We will click OK
  • Next, we will name our table as SalesPivotTable in the name box below file as shown below

Figure 4- Data Table

  • We will rename our sheet as “Data”

Steps to Write the VBA Code


Declare Variables


We need to declare the variables in the code to define different aspects:

  • PSheet: This implies that we want to create a sheet for a new pivot table
  • DSheet: To apply a datasheet
  • PChache: Apply as a name for pivot table cache
  • PTable: Apply a name for our pivot table
  • PRange: This defines the source data range (the range of our table, A3:F61)
  • LastRow and LastCol: We use this to acquire the last row and column of our data range.

Figure 5 – Declare all variables


Insert a New Worksheet


We will create a code for excel to place our Pivot Table in a blank sheet. With this code, we will insert a new worksheet titled “Pivot table.” Our values will be set as PSheet Variable to Pivot table Worksheet and DSheet to source data Worksheet. Whenever we want to change the name of the worksheet, we can do so from the code. We must note that if there is a worksheet named PivotTable, the code will delete it before creating the Pivot Table with VBA Code.

Figure 6 – Insert a New Worksheet


Defining Data Range


We will specify the range of the data from our source worksheet. This code will identify an entire data and not a fixed source range. The code will start from the first cell of the first row. It moves down to the last row, and then, to the last column. It will update itself, not minding how large or small our source data is.

Figure 7 – Define data range


Create Pivot Cache


Excel automatically creates a Pivot table Cache for us without asking. With VBA, we have to write the code for this by first defining a pivot cache through the data source. Also, we will define the cell address of the current inserted worksheet to create the pivot table.

Figure 8 – Create Pivot Cache


Create a Blank Pivot Table


This code will enable us to have a Blank Pivot Table before we select the fields that we want. We can alter it within the code at any time.

Figure 9 – Create a Blank Pivot Table


Insert Rows and Columns


Because we normally insert rows and columns, in the same manner, we will write code to do so. We will add years and month (Date) to the rows field and Zone to the column field. We will ensure that there is a position number to identify the sequence of fields, especially when we want to add more than one array to the same field.

Figure 10 – Insert Rows and Columns


Insert Data Field


We will define the value field for our pivot table. For example, we may use the xlsum to depict the sum values. We will use the (,) separator to identify values as a number.

Figure 11 – Insert Values Data Field


Format Pivot table


Lastly, we will also need a code to format our pivot table especially when we wish to change the formatting style of the Pivot table within the code. For our example, we will apply rows strips and “Pivot Style Medium 9”.

Figure 12 – Format Pivot Table


Run the Macro code to Create a Pivot table


Now, we have finished creating the VBA Code, we can run our code to create a pivot table.

Figure 13 – Run the Macro code

  • When we click on RUN, we will be instantly presented with the Pivot Table field, here, we will select “More Tables”, then Yes

Figure 14 – Select Worksheet

  • Lastly, we will select “SalesPivotTable” and press OK

Figure 15 – Finished Pivot Table with Macro Code

Full Pivot Table Macro Code

Sub CreatePivottablewithVBA()

'Declare Variables

Dim PSheet As Worksheet

Dim DSheet As Worksheet

Dim PCache As PivotCache

Dim PTable As PivotTable

Dim PRange As Range

Dim LastRow As Long

Dim LastCol As Long

'Insert a New Blank Worksheet

On Error Resume Next

Application.DisplayAlerts = False


Sheets.Add Before:=ActiveSheet

ActiveSheet.Name = "PivotTable"

Application.DisplayAlerts = True

Set PSheet = Worksheets("PivotTable")

Set DSheet = Worksheets("Data")

'Define Data Range

LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row

LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache

Set PCache = ActiveWorkbook.PivotCaches.Create _

(SourceType:=xlDatabase, SourceData:=PRange). _

CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _


'Insert Blank Pivot Table

Set PTable = PCache.CreatePivotTable _

(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

'Insert Row Fields

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year")

.Orientation = xlRowField

.Position = 1

End With


With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month")

.Orientation = xlRowField

.Position = 2

End With


'Insert Column Fields

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone")

.Orientation = xlColumnField

.Position = 1

End With


'Insert Data Field

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount")

.Orientation = xlDataField

.Position = 1

.Function = xlSum

.NumberFormat = "#,##0"

.Name = "Revenue "

End With


'Format Pivot Table

ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True

ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"


End Sub

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment