Excel VBA Create Pivot Table: Step-by-Step Guide and 4 Code Examples

In this VBA Tutorial, you learn how to create a Pivot Table with different destinations (both worksheet or workbook) and from both static and dynamic data ranges.

This VBA Tutorial is accompanied by Excel workbooks containing the data and macros I use in the examples below. You can get immediate free access to these example workbooks by clicking the button below.

Link to get VBA code examples to create Pivot Tables

Use the following Table of Contents to navigate to the section you’re interested in.

Table of Contents

Related VBA and Macro Tutorials
#1: Create Pivot Table in Existing Sheet
#2: Create Pivot Table in New Sheet
#3: Create Pivot Table in New Workbook
#4: Create Pivot Table from Dynamic Range
References to VBA Constructs Used in this VBA Tutorial
Comments, Suggestions or Questions?

#1: Create Pivot Table in Existing Sheet

VBA Code to Create Pivot Table in Existing Sheet

To create a Pivot Table in an existing sheet with VBA, use a statement with the following structure:

Workbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceWorksheetName & "!" & SourceDataAddress).createPivotTable TableDestination:=DestinationWorksheetName & "!" & DestinationRangeAddress, TableName:="NewPivotTable"

Process Followed by VBA Code

VBA Statement Explanation

Item: Workbook.
- VBA Construct: Workbook object.
- Description: Represents the Excel workbook containing the source (SourceWorksheet) and destination worksheets (DestinationWorksheet) you work with. For purposes of this structure, both the source and destination worksheet are in the same workbook.
  
  Use properties such Application.Workbooks, Application.ThisWorkbook and Application.ActiveWorkbook to return this Workbook object.
Item: PivotCaches
- VBA Construct: Workbook.PivotCaches method.
- Description: Returns the PivotCaches collection representing all the Pivot Table caches within Workbook.
Item: Create.
- VBA Construct: PivotCaches.Create method.
- Description: Creates a new PivotCache object representing the memory cache for the Pivot Table you create.
Item: SourceType:=xlDatabase
- VBA Construct: SourceType parameter of the PivotCaches.Create method.
- Description: Sets the data source of the Pivot Table you create to an Excel list or database (xlDatabase).
  
  Use the constants within the xlPivotTableSourceType enumeration for purposes of specifying a different data source. Nonetheless, setting SourceType to xlPivotTable (representing the same data source as another Pivot Table) or xlScenario (representing scenarios created using the Scenario Manager) generally results in a run-time error.
Item: SourceData:=SourceWorksheetName & “!” & SourceDataAddress.
- VBA Construct: SourceData parameter of the PivotCaches.Create method.
- Description: Specifies the data source for the Pivot Table cache.
  
  If you use the statement structure specified within this VBA Tutorial and explicitly declare variables to represent SourceWorksheetName and SourceDataAddress, use the String data type. Within this structure, SourceData is specified as follows:
  - SourceWorksheetName: Name of the worksheet containing the source data.
    
    If necessary, use the Worksheet.Name property to return a string representing the worksheet’s name.
  - &: Concatenation operator.
  - SourceDataAddress: Address of the cell range containing the source data.
    
    If necessary, use the Range.Address property to return a string representing the cell range reference.
  SourceData is of the Variant data type. However, Microsoft’s documentation recommends the following:
  - Either (i) using a string to specify the worksheet and cell range (as above), or (ii) setting up a named range and passing the name as a string.
  - Avoid passing a Range object, as this may result in unexpected “type mismatch” errors.
Item: createPivotTable
- VBA Construct: PivotCache.CreatePivotTable method.
- Description: Creates a Pivot Table based on the PivotCache created by the PivotCaches.Create method.
Item: TableDestination:=DestinationWorksheetName & “!” & DestinationRangeAddress
- VBA Construct: TableDestination parameter of PivotCache.CreatePivotTable method.
- Description: Specifies the cell in the upper-left corner of the cell range where the Pivot Table you create is located.
  
  If you use the statement structure specified within this VBA Tutorial and explicitly declare variables to represent DestinationWorksheetName and DestinationRangeAddress, use the String data type. Within this structure, TableDestination is specified as follows:
  - DestinationWorksheetName: Name of the destination worksheet where the Pivot Table you create is located.
    
    If necessary, use the Worksheet.Name property to return a string representing the worksheet’s name.
  - &: Concatenation operator.
  - DestinationRangeAddress: Address of the cell in the upper-left corner of the cell range where the Pivot Table you create is located.
    
    If necessary, use the Range.Address property to return a string representing the cell range reference.
Item: TableName:=”NewPivotTable”
- VBA Construct: TableName parameter of the PivotCache.CreatePivotTable method.
- Description: Specifies the name of the Pivot Table you create as “NewPivotTable”.
  
  If you explicitly declare a variable to represent NewPivotTable, use the String data type and omit the quotes included above (” “).

Macro Example

The following macro creates a new Pivot Table in an existing worksheet (PivotTable).

Sub createPivotTableExistingSheet()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-pivot-table/

    'declare variables to hold row and column numbers that define source data cell range
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long

    'declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String

    'declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable

    'identify source and destination worksheets
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("Data")
        Set myDestinationWorksheet = .Worksheets("PivotTable")
    End With

    'obtain address of destination cell range
    myDestinationRange = myDestinationWorksheet.Range("A5").Address(ReferenceStyle:=xlR1C1)

    'identify row and column numbers that define source data cell range
    myFirstRow = 5
    myLastRow = 20005
    myFirstColumn = 1
    myLastColumn = 6

    'obtain address of source data cell range
    With mySourceWorksheet.Cells
        mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    End With

    'create Pivot Table cache and create Pivot Table report based on that cache
    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableExistingSheet")

    'add, organize and format Pivot Table fields
    With myPivotTable
        .PivotFields("Item").Orientation = xlRowField
        With .PivotFields("Units Sold")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
        With .PivotFields("Sales Amount")
            .Orientation = xlDataField
            .Position = 2
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
    End With

End Sub

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, the macro creates a Pivot Table in the “PivotTable” worksheet.

#2: Create Pivot Table in New Sheet

VBA Code to Create Pivot Table in New Sheet

To create a Pivot Table in a new sheet with VBA, use a macro with the following statement structure:

Dim DestinationWorksheet As Worksheet
Set DestinationWorksheet = Worksheets.Add
Workbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceWorksheetName & "!" & SourceDataAddress).createPivotTable TableDestination:=DestinationWorksheet.Name & "!" & DestinationRangeAddress, TableName:="NewPivotTable"

Process Followed by VBA Code

VBA Statement Explanation

Line #1: Dim DestinationWorksheet As Worksheet

Item: Dim DestinationWorksheet As Worksheet.
- VBA Construct: Dim statement.
- Description: Declares the DestinationWorksheet object variable as of the Worksheet object data type.
  
  DestinationWorksheet represents the new worksheet (line #2 below) where the Pivot Table you create (line #3 below) is located.

Line #2: Set DestinationWorksheet = Worksheets.Add

Item: Set.
- VBA Construct: Set statement.
- Description: Assigns the reference to the Worksheet object returned by the Worksheets.Add method to the DestinationWorksheet object variable.
Item: DestinationWorksheet.
- VBA Construct: Object variable of the Worksheet object data type.
- Description: Represents the new worksheet where the Pivot Table you create (line #3 below) is located.
Item: =.
- VBA Construct: Assignment operator.
- Description: Assigns the reference to the Worksheet object returned by the Worksheets.Add method to the DestinationWorksheet object variable.
Item: Worksheets.
- VBA Construct: Worksheets collection.
- Description: The collection containing all the Worksheet objects (each representing a worksheet) within the workbook your work with.
Item: Add.
- VBA Construct: Worksheets.Add method.
- Description: Creates a new worksheet. This is the worksheet where the Pivot Table you create (line #3 below) is located.

Line #3: Workbook.PivotCaches.Create( SourceType:=xlDatabase, SourceData:=SourceWorksheetName & “!” & SourceDataAddress).createPivotTable TableDestination:=DestinationWorksheetName & “!” & DestinationRangeAddress, TableName:=”NewPivotTable”

Item: Workbook.
- VBA Construct: Workbook object.
- Description: Represents the Excel workbook containing the source (SourceWorksheet) and destination worksheets (DestinationWorksheet) you work with. For purposes of this structure, both the source and destination worksheet are in the same workbook.
  
  Use properties such Application.Workbooks, Application.ThisWorkbook and Application.ActiveWorkbook to return this Workbook object.
Item: PivotCaches
- VBA Construct: Workbook.PivotCaches method.
- Description: Returns the PivotCaches collection representing all the Pivot Table caches within Workbook.
Item: Create.
- VBA Construct: PivotCaches.Create method.
- Description: Creates a new PivotCache object representing the memory cache for the Pivot Table you create.
Item: SourceType:=xlDatabase
- VBA Construct: SourceType parameter of the PivotCaches.Create method.
- Description: Sets the data source of the Pivot Table you create to an Excel list or database (xlDatabase).
  
  Use the constants within the xlPivotTableSourceType enumeration for purposes of specifying a different data source. Nonetheless, setting SourceType to xlPivotTable (representing the same data source as another Pivot Table) or xlScenario (representing scenarios created using the Scenario Manager) generally results in a run-time error.
Item: SourceData:=SourceWorksheetName & “!” & SourceDataAddress.
- VBA Construct: SourceData parameter of the PivotCaches.Create method.
- Description: Specifies the data source for the Pivot Table cache.
  
  If you use the statement structure specified within this VBA Tutorial and explicitly declare variables to represent SourceWorksheetName and SourceDataAddress, use the String data type. Within this structure, SourceData is specified as follows:
  - SourceWorksheetName: Name of the worksheet containing the source data.
    
    If necessary, use the Worksheet.Name property to return a string representing the worksheet’s name.
  - &: Concatenation operator.
  - SourceDataAddress: Address of the cell range containing the source data.
    
    If necessary, use the Range.Address property to return a string representing the cell range reference.
  SourceData is of the Variant data type. However, Microsoft’s documentation recommends the following:
  - Either (i) using a string to specify the worksheet and cell range (as above), or (ii) setting up a named range and passing the name as a string.
  - Avoid passing a Range object, as this may result in unexpected “type mismatch” errors.
Item: createPivotTable
- VBA Construct: PivotCache.CreatePivotTable method.
- Description: Creates a Pivot Table based on the PivotCache created by the PivotCaches.Create method.
Item: TableDestination:=DestinationWorksheet.Name & “!” & DestinationRangeAddress
- VBA Construct: TableDestination parameter of PivotCache.CreatePivotTable method.
- Description: Specifies the cell in the upper-left corner of the cell range where the Pivot Table you create is located.
  
  If you use the statement structure specified within this VBA Tutorial and explicitly declare a variable to represent DestinationRangeAddress, use the String data type. Within this structure, TableDestination is specified as follows:
  - DestinationWorksheet.Name: Worksheet.Name property.
    
    Returns a string representing the name of DestinationWorksheet. DestinationWorksheet is the new worksheet where the Pivot Table you create is located.
  - &: Concatenation operator.
  - DestinationRangeAddress: Address of the cell in the upper-left corner of the cell range where the Pivot Table you create is located.
    
    If necessary, use the Range.Address property to return a string representing the cell range reference.
Item: TableName:=”NewPivotTable”
- VBA Construct: TableName parameter of the PivotCache.CreatePivotTable method.
- Description: Specifies the name of the Pivot Table you create as “NewPivotTable”.
  
  If you explicitly declare a variable to represent NewPivotTable, use the String data type and omit the quotes included above (” “).

Macro Example

The following macro creates a new Pivot Table in a new worksheet.

Sub createPivotTableNewSheet()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-pivot-table/

    'declare variables to hold row and column numbers that define source data cell range
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long

    'declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String

    'declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable

    'identify source and destination worksheets. Add destination worksheet
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("Data")
        Set myDestinationWorksheet = .Worksheets.Add
    End With

    'obtain address of destination cell range
    myDestinationRange = myDestinationWorksheet.Range("A5").Address(ReferenceStyle:=xlR1C1)

    'identify row and column numbers that define source data cell range
    myFirstRow = 5
    myLastRow = 20005
    myFirstColumn = 1
    myLastColumn = 6

    'obtain address of source data cell range
    With mySourceWorksheet.Cells
        mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    End With

    'create Pivot Table cache and create Pivot Table report based on that cache
    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableNewSheet")

    'add, organize and format Pivot Table fields
    With myPivotTable
        .PivotFields("Item").Orientation = xlRowField
        With .PivotFields("Units Sold")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
        With .PivotFields("Sales Amount")
            .Orientation = xlDataField
            .Position = 2
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
    End With

End Sub

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, the macro creates a Pivot Table in a new worksheet (Sheet4).

#3: Create Pivot Table in New Workbook

VBA Code to Create Pivot Table in New Workbook

To create a Pivot Table in a new workbook with VBA, use a macro with the following statement structure:

Dim DestinationWorkbook As Workbook
Set DestinationWorkbook = Workbooks.Add
DestinationWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="[" & SourceWorkbookName & "]" & SourceWorksheetName & "!" & SourceDataAddress).createPivotTable TableDestination:="[" & DestinationWorkbook.Name & "]" & DestinationWorkbook.Worksheets(1).Name & "!" & DestinationRangeAddress, TableName:="NewPivotTable"

Process Followed by VBA Code

VBA Statement Explanation

Line #1: Dim DestinationWorkbook As Workbook

Item: Dim DestinationWorkbook As Workbook.
- VBA Construct: Dim statement.
- Description: Declares the Destination Workbook object variable as of the Workbook object data type.
  
  DestinationWorkbook represents the new workbook (line #2 below) where the Pivot Table you create (line #3 below) is located.

Line #2: Set DestinationWorkbook = Workbooks.Add

Item: Set.
- VBA Construct: Set statement.
- Description: Assigns the reference to the Workbook object returned by the Workbooks.Add method to the DestinationWorkbook object variable.
Item: DestinationWorkbook.
- VBA Construct: Object variable of the Workbook object data type.
- Description: Represents the new workbook where the Pivot Table you create (line #3 below) is located.
Item: =.
- VBA Construct: Assignment operator.
- Description: Assigns the reference to the Workbook object returned by the Workbooks.Add method to the DestinationWorkbook object variable.
Item: Workbooks.
- VBA Construct: Workbooks collection.
- Description: The collection containing all the Workbook objects (each representing a workbook) currently open in Excel.
Item: Add.
- VBA Construct: Workbooks.Add method.
- Description: Creates a new workbook. This is the workbook where the Pivot Table you create (line #3 below) is located.

Line #3: DestinationWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=”[” & SourceWorkbookName & “]” & SourceWorksheetName & “!” & SourceDataAddress).createPivotTable TableDestination:=”[” & DestinationWorkbook.Name & “]” & DestinationWorkbook.Worksheets(1).Name & “!” & DestinationRangeAddress, TableName:=”NewPivotTable”

Item: DestinationWorkbook.
- VBA Construct: Object variable of the Workbook object data type.
- Description: Represents the new workbook where the Pivot Table you create is located.
Item: PivotCaches
- VBA Construct: Workbook.PivotCaches method.
- Description: Returns the PivotCaches collection representing all the Pivot Table caches within DestinationWorkbook.
Item: Create.
- VBA Construct: PivotCaches.Create method.
- Description: Creates a new PivotCache object representing the memory cache for the Pivot Table you create.
Item: SourceType:=xlDatabase
- VBA Construct: SourceType parameter of the PivotCaches.Create method.
- Description: Sets the data source of the Pivot Table you create to an Excel list or database (xlDatabase).
  
  Use the constants within the xlPivotTableSourceType enumeration for purposes of specifying a different data source. Nonetheless, setting SourceType to xlPivotTable (representing the same data source as another Pivot Table) or xlScenario (representing scenarios created using the Scenario Manager) generally results in a run-time error.
Item: SourceData:=”[” & SourceWorkbookName & “]” & SourceWorksheetName & “!” & SourceDataAddress.
- VBA Construct: SourceData parameter of the PivotCaches.Create method.
- Description: Specifies the data source for the Pivot Table cache.
  
  If you use the statement structure specified within this VBA Tutorial and explicitly declare variables to represent SourceWorkbookName, SourceWorksheetName and SourceDataAddress, use the String data type. Within this structure, SourceData is specified as follows:
  - SourceWorkbookName: Name of the workbook containing the source data.
    
    If necessary, use the Workbook.Name property to return a string representing the workbook’s name.
  - SourceWorksheetName: Name of the worksheet containing the source data.
    
    If necessary, use the Worksheet.Name property to return a string representing the worksheet’s name.
  - SourceDataAddress: Address of the cell range containing the source data.
    
    If necessary, use the Range.Address property to return a string representing the cell range reference.
  - &: Concatenation operator.
  SourceData is of the Variant data type. However, Microsoft’s documentation recommends the following:
  - Either (i) using a string to specify the worksheet and cell range (as above), or (ii) setting up a named range and passing the name as a string.
  - Avoid passing a Range object, as this may result in unexpected “type mismatch” errors.
Item: createPivotTable
- VBA Construct: PivotCache.CreatePivotTable method.
- Description: Creates a Pivot Table based on the PivotCache created by the PivotCaches.Create method.
Item: TableDestination:=”[” & DestinationWorkbook.Name & “]” & DestinationWorkbook.Worksheets(1).Name & “!” & DestinationRangeAddress.
- VBA Construct: TableDestination parameter of PivotCache.CreatePivotTable method.
- Description: Specifies the cell in the upper-left corner of the cell range where the Pivot Table you create is located.
  
  If you use the statement structure specified within this VBA Tutorial and explicitly declare a variable to represent DestinationRangeAddress, use the String data type. Within this structure, TableDestination is specified as follows:
  - DestinationWorkbook.Name: Workbook.Name property.
    
    Returns a string representing the name of DestinationWorkbook. DestinationWorkbook is the new workbook where the Pivot Table you create is located.
  - DestinationWorkbook.Worksheets(1).Name: Workbook.Worksheets property and Worksheet.Name property.
    
    The Workbook.Worksheets property (DestinationWorkbook.Worksheets(1)) returns a Worksheet object representing the first worksheet (Worksheets(1)) of DestinationWorkbook. The Worksheet.Name property returns a string representing the name of that worksheet, which is where the Pivot Table you create is located.
  - DestinationRangeAddress: Address of the cell in the upper-left corner of the cell range where the Pivot Table you create is located.
    
    If necessary, use the Range.Address property to return a string representing the cell range reference.
  - &: Concatenation operator.
Item: TableName:=”NewPivotTable”
- VBA Construct: TableName parameter of the PivotCache.CreatePivotTable method.
- Description: Specifies the name of the Pivot Table you create as “NewPivotTable”.
  
  If you explicitly declare a variable to represent NewPivotTable, use the String data type and omit the quotes included above (” “).

Macro Example

The following macro creates a new Pivot Table in a new workbook.

Sub createPivotTableNewWorkbook()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-pivot-table/

    'declare variables to hold row and column numbers that define source data cell range
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long

    'declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String

    'declare object variables to hold references to destination workbook, source and destination worksheets, and new Pivot Table
    Dim myDestinationWorkbook As Workbook
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable

    'add and identify destination worksheet
    Set myDestinationWorkbook = Workbooks.Add

    'identify source and destination worksheets
    Set mySourceWorksheet = ThisWorkbook.Worksheets("Data")
    Set myDestinationWorksheet = myDestinationWorkbook.Worksheets(1)

    'obtain address of destination cell range
    myDestinationRange = myDestinationWorksheet.Range("A5").Address(ReferenceStyle:=xlR1C1)

    'identify row and column numbers that define source data cell range
    myFirstRow = 5
    myLastRow = 20005
    myFirstColumn = 1
    myLastColumn = 6

    'obtain address of source data cell range
    With mySourceWorksheet.Cells
        mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    End With

    'create Pivot Table cache and create Pivot Table report based on that cache
    Set myPivotTable = myDestinationWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="[" & ThisWorkbook.Name & "]" & mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:="[" & myDestinationWorkbook.Name & "]" & myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableNewWorkbook")

    'add, organize and format Pivot Table fields
    With myPivotTable
        .PivotFields("Item").Orientation = xlRowField
        With .PivotFields("Units Sold")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
        With .PivotFields("Sales Amount")
            .Orientation = xlDataField
            .Position = 2
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
    End With

End Sub

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, the macro creates a Pivot Table in a new workbook.

#4: Create Pivot Table from Dynamic Range

VBA Code to Create Pivot Table from Dynamic Range

To create a Pivot Table from a dynamic range (where the number of the last row and last column may vary) with VBA, use a macro with the following statement structure:

Dim LastRow As Long
Dim LastColumn As Long
Dim SourceDataAddress As String
With SourceWorksheet.Cells
LastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
SourceDataAddress = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn)).Address(ReferenceStyle:=xlR1C1)
End With
Workbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceWorksheetName & "!" & SourceDataAddress).createPivotTable TableDestination:=DestinationWorksheetName & "!" & DestinationRangeAddress, TableName:="NewPivotTable"

Process Followed by VBA Code

VBA Statement Explanation

Line #1: Dim LastRow As Long

Item: Dim LastRow As Long.
- VBA Construct: Dim statement.
- Description: Declares the LastRow variable as of the Long data type.
  
  LastRow holds the number of the last row with data in the worksheet containing the source data (SourceWorksheet).

Line #2: Dim LastColumn As Long

Item: Dim LastColumn As Long.
- VBA Construct: Dim statement.
- Description: Declares the LastColumn variable as of the Long data type.
  
  LastColumn holds the number of the last column with data in the worksheet containing the source data (SourceWorksheet).

Line #3: Dim SourceDataAddress As String

Item: Dim SourceDataAddress As String.
- VBA Construct: Dim statement.
- Description: Declares the SourceDataAddress variable as of the String data type.
  
  SourceDataAddress holds the address of the cell range containing the source data.

Lines #4 and #8: With SourceWorksheet.Cells | End With

Item: With… End With.
- VBA Construct: With… End With statement.
- Description: Statements within the With… End With statement (lines #5 through #7 below) are executed on the Range object returned by SourceWorksheet.Cells.
Item: SourceWorksheet.
- VBA Construct: Worksheet object.
- Description: Represents the worksheet containing the source data. If you explicitly declare an object variable to represent SourceWorksheet, use the Worksheet object data type.
Item: Cells.
- VBA Construct: Worksheets.Cells property.
- Description: Returns a Range object representing all the cells in SourceWorksheet.

Line #5: LastRow = .Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Item: LastRow.
- VBA Construct: Variable of the long data type.
- Description: LastRow holds the number of the last row with data in SourceWorksheet.
Item: =.
- VBA Construct: Assignment operator.
- Description: Assigns the row number returned by the Range.Row property to the LastRow variable.
Item: Find.
- VBA Construct: Range.Find method.
- Description: Returns a Range object representing the first cell where the information specified by the parameters of the Range.Find method (What, LookIn, LookAt, SearchOrder and SearchDirection) is found. Within this macro structure, this Range object represents the last cell with data in the last row with data in SourceWorksheet.
Item: What:=”*”.
- VBA Construct: What parameter of the Range.Find method.
- Description: Specifies the data the Range.Find method searches for. The asterisk (*) is a wildcard and, therefore, the Range.Find method searches for any character sequence.
Item: LookIn:=xlFormulas.
- VBA Construct: LookIn parameter of the Range.Find method.
- Description: Specifies that the Range.Find method looks in formulas (xlFormulas).
Item: LookAt:=xlPart.
- VBA Construct: LookAt parameter of the Range.Find method.
- Description: Specifies that the Range.Find method looks at (and matches) a part (xlPart) of the search data.
Item: SearchOrder:=xlByRows.
- VBA Construct: SearchOrder parameter of the Range.Find method.
- Description: Specifies that the Range.Find method searches by rows (xlByRows).
Item: SearchDirection:=xlPrevious.
- VBA Construct: SearchDirection parameter of the Range.Find method.
- Description: Specifies that the Range.Find method searches for the previous match (xlPrevious).
Item: Row.
- VBA Construct: Range.Row property.
- Description: Returns the row number of the Range object returned by the Range.Find method. Within this macro structure, this row number corresponds to the last row with data in SourceWorksheet.

Line #6: LastColumn = .Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Item: LastColumn.
- VBA Construct: Variable of the long data type.
- Description: Variable of the long data type.
  
  LastColumn holds the number of the last column with data in SourceWorksheet.
Item: =.
- VBA Construct: Assignment operator.
- Description: Assigns the column number returned by the Range.Column property to the LastColumn variable.
Item: Find.
- VBA Construct: Range.Find method.
- Description: Returns a Range object representing the first cell where the information specified by the parameters of the Range.Find method (What, LookIn, LookAt, SearchOrder and SearchDirection) is found. Within this macro structure, this Range object represents the last cell with data in the last column with data in SourceWorksheet.
Item: What:=”*”.
- VBA Construct: What parameter of the Range.Find method.
- Description: Specifies the data the Range.Find method searches for. The asterisk (*) is a wildcard and, therefore, the Range.Find method searches for any character sequence.
Item: LookIn:=xlFormulas.
- VBA Construct: LookIn parameter of the Range.Find method.
- Description: Specifies that the Range.Find method looks in formulas (xlFormulas).
Item: LookAt:=xlPart.
- VBA Construct: LookAt parameter of the Range.Find method.
- Description: Specifies that the Range.Find method looks at (and matches) a part (xlPart) of the search data.
Item: SearchOrder:=xlByColumns.
- VBA Construct: SearchOrder parameter of the Range.Find method.
- Description: Specifies that the Range.Find method searches by columns (xlByColumns).
Item: SearchDirection:=xlPrevious.
- VBA Construct: SearchDirection parameter of the Range.Find method.
- Description: Specifies that the Range.Find method searches for the previous match (xlPrevious).
Item: Column.
- VBA Construct: Range.Column property.
- Description: Returns the column number of the Range object returned by the Range.Find method. Within this macro structure, this column number corresponds to the last column with data in SourceWorksheet.

Line #7: SourceDataAddress = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn)).Address(ReferenceStyle:=xlR1C1)

Item: SourceDataAddress.
- VBA Construct: Variable of the String data type.
- Description: SourceDataAddress holds the address of the cell range containing the source data.
Item: =.
- VBA Construct: Assignment operator.
- Description: Assigns the string returned by the Range.Address property to the SourceDataAddress variable.
Item: Range.
- VBA Construct: Range.Range property.
- Description: Returns a Range object representing the cell range containing the source data. Within this macro structure, the Range property is applied to the Range object returned by the Worksheet.Cells property in the opening statement of the With… End With statement (line #4 above).
Item: Cells(1, 1).
- VBA Construct: Cells1 parameter of the Range.Range property, Range.Cells property and Range.Item property.
- Description: The Cells1 parameter of the Range.Range property specifies the cell in the upper-left corner of the cell range. Within this macro structure, Cells1 is the Range object returned by the Range.Cells property.
  
  The Range.Cells property returns all the cells within the cell range represented by the Range object returned by the Worksheet.Cells property in the opening statement of the With… End With statement (line #4 above). The Range.Item property is the default property and returns a Range object representing the cell on the first row and first column (Cells(1, 1)) of the cell range it works with.
  
  Since the Worksheet.Cells property in line #4 above returns all the cells in SourceWorksheet, this is cell A1 of SourceWorksheet.
Item: Cells(LastRow, LastColumn).
- VBA Construct: Cells2 parameter of the Range.Range property, Range.Cells property and Range.Item property.
- Description: The Cells2 parameter of the Range.Range property specifies the cells in the lower-right corner of the cell range. Within this macro structure, Cells2 is the Range object returned by the Range.Cells property.
  
  The Range.Cells property returns all the cells within the cell range represented by the Range object returned by the Worksheet.Cells property in the opening statement of the With… End With statement (line #4 above). The Range.Item property is the default property and returns a Range object representing the cell located at the intersection of LastRow and LastColumn.
  
  Since the Worksheet.Cells property in line #4 above returns all the cells in SourceWorksheet, this is the cell located at the intersection of the last row and the last column (or the last cell with data) within SourceWorksheet.
Item: Address.
- VBA Construct: Range.Address property.
- Description: Returns a string representing the cell range reference to the Range object returned by the Range.Range property.
Item: ReferenceStyle:=xlR1C1.
- VBA Construct: ReferenceStyle parameter of the Range.Address property.
- Description: Specifies that the cell range reference returned by the Range.Address property is in the R1C1 reference style.

Line #9: Workbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceWorksheetName & “!” & SourceDataAddress).createPivotTable TableDestination:=DestinationWorksheetName & “!” & DestinationRangeAddress, TableName:=”NewPivotTable”[=]

Item: Workbook.
- VBA Construct: Workbook object.
- Description: Represents the Excel workbook containing the source (SourceWorksheet) and destination worksheets (DestinationWorksheet) you work with. For purposes of this structure, both the source and destination worksheet are in the same workbook.
  
  Use properties such Application.Workbooks, Application.ThisWorkbook and Application.ActiveWorkbook to return this Workbook object.
Item: PivotCaches
- VBA Construct: Workbook.PivotCaches method.
- Description: Returns the PivotCaches collection representing all the Pivot Table caches within Workbook.
Item: Create.
- VBA Construct: PivotCaches.Create method.
- Description: Creates a new PivotCache object representing the memory cache for the Pivot Table you create.
Item: SourceType:=xlDatabase
- VBA Construct: SourceType parameter of the PivotCaches.Create method.
- Description: Sets the data source of the Pivot Table you create to an Excel list or database (xlDatabase).
  
  Use the constants within the xlPivotTableSourceType enumeration for purposes of specifying a different data source. Nonetheless, setting SourceType to xlPivotTable (representing the same data source as another Pivot Table) or xlScenario (representing scenarios created using the Scenario Manager) generally results in a run-time error.
Item: SourceData:=SourceWorksheetName & “!” & SourceDataAddress.
- VBA Construct: SourceData parameter of the PivotCaches.Create method.
- Description: Specifies the data source for the Pivot Table cache.
  
  If you use the statement structure specified within this VBA Tutorial and explicitly declare variables to represent SourceWorksheetName and SourceDataAddress, use the String data type. Within this structure, SourceData is specified as follows:
  - SourceWorksheetName: Name of the worksheet containing the source data.
    
    If necessary, use the Worksheet.Name property to return a string representing the worksheet’s name.
  - &: Concatenation operator.
  - SourceDataAddress: Variable of the String data type.
    
    SourceDataAddress holds the address of the cell range containing the source data.
Item: createPivotTable
- VBA Construct: PivotCache.CreatePivotTable method.
- Description: Creates a Pivot Table based on the PivotCache created by the PivotCaches.Create method.
Item: TableDestination:=DestinationWorksheetName & “!” & DestinationRangeAddress
- VBA Construct: TableDestination parameter of PivotCache.CreatePivotTable method.
- Description: Specifies the cell in the upper-left corner of the cell range where the Pivot Table you create is located.
  
  If you use the statement structure specified within this VBA Tutorial and explicitly declare variables to represent DestinationWorksheetName and DestinationRangeAddress, use the String data type. Within this structure, TableDestination is specified as follows:
  - DestinationWorksheetName: Name of the destination worksheet where the Pivot Table you create is located.
    
    If necessary, use the Worksheet.Name property to return a string representing the worksheet’s name.
  - &: Concatenation operator.
  - DestinationRangeAddress: Address of the cell in the upper-left corner of the cell range where the Pivot Table you create is located.
    
    If necessary, use the Range.Address property to return a string representing the cell range reference.
Item: TableName:=”NewPivotTable”
- VBA Construct: TableName parameter of the PivotCache.CreatePivotTable method.
- Description: Specifies the name of the Pivot Table you create as “NewPivotTable”.
  
  If you explicitly declare a variable to represent NewPivotTable, use the String data type and omit the quotes included above (” “).

Macro Example

The macro below creates a new Pivot Table from a dynamic range, where the last row and column is dynamically identified.

Sub createPivotTableDynamicRange()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-pivot-table/

    'declare variables to hold row and column numbers that define source data cell range
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long

    'declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String

    'declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable

    'identify source and destination worksheets
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("Data")
        Set myDestinationWorksheet = .Worksheets("DynamicRange")
    End With

    'obtain address of destination cell range
    myDestinationRange = myDestinationWorksheet.Range("A5").Address(ReferenceStyle:=xlR1C1)

    'identify first row and first column of source data cell range
    myFirstRow = 5
    myFirstColumn = 1

    With mySourceWorksheet.Cells

        'find last row and last column of source data cell range
        myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

        'obtain address of source data cell range
        mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)

    End With

    'create Pivot Table cache and create Pivot Table report based on that cache
    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableExistingSheet")

    'add, organize and format Pivot Table fields
    With myPivotTable
        .PivotFields("Item").Orientation = xlRowField
        With .PivotFields("Units Sold")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
        With .PivotFields("Sales Amount")
            .Orientation = xlDataField
            .Position = 2
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
    End With

End Sub

Effects of Executing Macro Example

The following GIF illustrates the results of executing this macro example. As expected, the macro creates a Pivot Table from a dynamic range.

References to VBA Constructs Used in this VBA Tutorial

Use the following links to visit the appropriate webpage in the Microsoft Developer Network:

Identify or create the workbooks you work with:
Identify or create the worksheets you work with:
Identify the cell ranges you work with:
- Range object.
- Worksheet.Range property.
- Worksheet.Cells property.
- Range.Range property.
- Range.Cells property.
- Range.Item property.
- Identify the last row or column with data in a worksheet:
Obtain the names and addresses of the workbooks, worksheets and cell ranges you work with:
Create a Pivot Table cache and Pivot Table report:
Work with Pivot Table fields:
Concatenate strings:
- Concatenation operators.
Work with variables and data types:
- Dim statement.
- Set statement.
- = operator.
- Data types:
  - Long data type.
  - String data type.
Simplify object references:
- With… End With statement.

Comments, Suggestions or Questions?

What macros and VBA constructs do you use to create Pivot Tables?
Do you have any questions regarding the macros or constructs I use in the VBA Tutorial above?
Would you change anything in the macro examples I use above?

If you have these, or any other comments, suggestions or questions, please take a few seconds to let me know in the comments. I would love to read your feedback. Thanks in advance!

Lefkios Paikousis

Hi! Great tutorial!
I was interested in the last part where you create a pivot table with dynamic range
Is it possible to reference a dynamic named range within the workbook?
I have a dynamic named range where the size (specifically the number of rows) is monitored by the offset function
Is it going to be faster than this or the same?

Thanks and again, great tutorial ! I really enjoyed it
Lefkios
- Jorge A. Gomez
  
  Hi Lefkios! Many thanks for your comment. I’m very happy to read you’ve found it useful and appreciate you taking the time to leave a comment!
  
  If I understand the situation you’re working on correctly, it should be possible to use a named range. However, I’m not sure if that (by itself) would solve any performance issues you may be experiencing.
  
  If you’re working with a named range, you can pass the name as a string when specifying the SourceData parameter of the PivotCaches.Create method. If this is what you want to do, you may be able to start with one of the examples above and simplify (i.e. delete) some sections. At the end of this comment I include a new version of the macro that creates a Pivot Table in a new worksheet from a named range (pivotTableData). You probably need to modify this further (for example, consider aspects such as the scope of the named range), but it may give you an idea of what I mean.
  
  If your workbook is too slow, part of the problem may lie on the OFFSET (volatile) function. From this point of view, the approach where the macro finds the last row when executed (instead of relying on OFFSET) may help. This is the approach taken by the fourth example in the Tutorial above (which creates a Pivot Table from a dynamic range).
  
  In my opinion, the 2 approaches (macro vs. dynamic range) are slightly different, with their own advantages and disadvantages. If you have further comments/questions, or if any of my comments above isn’t clear, please let me know.
  
  Thanks again for your visit and support!
  
  Jorge
  
  ———-
  
  Sub createPivotTableNewSheetNamedRange()
  
  ‘Source: https://powerspreadsheets.com/
  ‘For further information: https://powerspreadsheets.com/vba-create-pivot-table/
  
  ‘declare variables to hold source named range and destination cell range address
  Dim mySourceData As String
  Dim myDestinationRange As String
  
  ‘declare object variables to hold references to destination worksheet and new Pivot Table
  Dim myDestinationWorksheet As Worksheet
  Dim myPivotTable As PivotTable
  
  ‘identify destination worksheets. Add destination worksheet
  Set myDestinationWorksheet = ThisWorkbook.Worksheets.Add
  
  ‘obtain address of destination cell range
  myDestinationRange = myDestinationWorksheet.Range(“A5”).Address(ReferenceStyle:=xlR1C1)
  
  ‘specify address of source data cell range
  mySourceData = “pivotTableData”
  
  ‘create Pivot Table cache and create Pivot Table report based on that cache
  Set myPivotTable = ThisWorkbook.PivotCaches.Create( _
  SourceType:=xlDatabase, _
  SourceData:=mySourceData).CreatePivotTable( _
  TableDestination:=myDestinationWorksheet.Name & “!” & myDestinationRange, _
  TableName:=”PivotTableNewSheet”)
  
  ‘add, organize and format Pivot Table fields
  With myPivotTable
  .PivotFields(“Item”).Orientation = xlRowField
  With .PivotFields(“Units Sold”)
  .Orientation = xlDataField
  .Position = 1
  .Function = xlSum
  .NumberFormat = “#,##0.00”
  End With
  With .PivotFields(“Sales Amount”)
  .Orientation = xlDataField
  .Position = 2
  .Function = xlSum
  .NumberFormat = “#,##0.00”
  End With
  End With
  
  End Sub

Related VBA and Macro Tutorials

#1: Create Pivot Table in Existing Sheet

VBA Code to Create Pivot Table in Existing Sheet

Process Followed by VBA Code

VBA Statement Explanation

Macro Example

Effects of Executing Macro Example

#2: Create Pivot Table in New Sheet

VBA Code to Create Pivot Table in New Sheet

Process Followed by VBA Code

VBA Statement Explanation

Line #1: Dim DestinationWorksheet As Worksheet

Line #2: Set DestinationWorksheet = Worksheets.Add

Line #3: Workbook.PivotCaches.Create( SourceType:=xlDatabase, SourceData:=SourceWorksheetName & “!” & SourceDataAddress).createPivotTable TableDestination:=DestinationWorksheetName & “!” & DestinationRangeAddress, TableName:=”NewPivotTable”

Macro Example

Effects of Executing Macro Example

#3: Create Pivot Table in New Workbook

VBA Code to Create Pivot Table in New Workbook

Process Followed by VBA Code

VBA Statement Explanation

Line #1: Dim DestinationWorkbook As Workbook

Line #2: Set DestinationWorkbook = Workbooks.Add

Macro Example

Effects of Executing Macro Example

#4: Create Pivot Table from Dynamic Range

VBA Code to Create Pivot Table from Dynamic Range

Process Followed by VBA Code

VBA Statement Explanation

Line #1: Dim LastRow As Long

Line #2: Dim LastColumn As Long

Line #3: Dim SourceDataAddress As String

Lines #4 and #8: With SourceWorksheet.Cells | End With

Line #5: LastRow = .Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Line #6: LastColumn = .Find(What:=”*”, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Line #7: SourceDataAddress = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn)).Address(ReferenceStyle:=xlR1C1)

Line #9: Workbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceWorksheetName & “!” & SourceDataAddress).createPivotTable TableDestination:=DestinationWorksheetName & “!” & DestinationRangeAddress, TableName:=”NewPivotTable”[=]

Macro Example

Effects of Executing Macro Example

References to VBA Constructs Used in this VBA Tutorial

Comments, Suggestions or Questions?