Excel VBA Create Pivot Table: Step-by-Step Guide and 4 Examples to Create Pivot Tables with Macros

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 subscribing to the Power Spreadsheets Newsletter.

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

Related VBA and Macro Tutorials

The following VBA and Macro Tutorials may help you better understand and implement the contents below:

• General VBA constructs and structures:
  
  
  • Learn about commonly-used VBA terms here.
    
    
  • Learn about the Excel VBA Object Model here.
    
    
  • Learn how to work with variables here.
    
    
  • Learn about data types here.
    
    
  • Learn about the R1C1 reference-style here.
    
    
• Practical VBA applications and macro examples:
  
  
  • Learn how to create a new workbook here.
    
    
  • Learn how to find the last column with data here.
    
    
  • Learn about working with worksheets here.

You can find additional VBA and Macro Tutorials in the Archives.

#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

1. 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.
     
     
2. Item: PivotCaches
   
   
   • VBA Construct: Workbook.PivotCaches method.
     
     
   • Description: Returns the PivotCaches collection representing all the Pivot Table caches within Workbook.
     
     
3. Item: Create.
   
   
   • VBA Construct: PivotCaches.Create method.
     
     
   • Description: Creates a new PivotCache object representing the memory cache for the Pivot Table you create.
     
     
4. 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.
     
     
5. 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.
       
       
6. Item: createPivotTable
   
   
   • VBA Construct: PivotCache.CreatePivotTable method.
     
     
   • Description: Creates a Pivot Table based on the PivotCache created by the PivotCaches.Create method.
     
     
7. 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.
       
       
8. 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

1. 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

1. 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.
     
     
2. 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.
     
     
3. Item: =.
   
   
   • VBA Construct: Assignment operator.
     
     
   • Description: Assigns the reference to the Worksheet object returned by the Worksheets.Add method to the DestinationWorksheet object variable.
     
     
4. Item: Worksheets.
   
   
   • VBA Construct: Worksheets collection.
     
     
   • Description: The collection containing all the Worksheet objects (each representing a worksheet) within the workbook your work with.
     
     
5. 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”

1. 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.
     
     
2. Item: PivotCaches
   
   
   • VBA Construct: Workbook.PivotCaches method.
     
     
   • Description: Returns the PivotCaches collection representing all the Pivot Table caches within Workbook.
     
     
3. Item: Create.
   
   
   • VBA Construct: PivotCaches.Create method.
     
     
   • Description: Creates a new PivotCache object representing the memory cache for the Pivot Table you create.
     
     
4. 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.
     
     
5. 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.
       
       
6. Item: createPivotTable
   
   
   • VBA Construct: PivotCache.CreatePivotTable method.
     
     
   • Description: Creates a Pivot Table based on the PivotCache created by the PivotCaches.Create method.
     
     
7. 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.
       
       
8. 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

1. 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

1. 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.
     
     
2. 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.
     
     
3. Item: =.
   
   
   • VBA Construct: Assignment operator.
     
     
   • Description: Assigns the reference to the Workbook object returned by the Workbooks.Add method to the DestinationWorkbook object variable.
     
     
4. Item: Workbooks.
   
   
   • VBA Construct: Workbooks collection.
     
     
   • Description: The collection containing all the Workbook objects (each representing a workbook) currently open in Excel.
     
     
5. 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”

1. 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.
     
     
2. Item: PivotCaches
   
   
   • VBA Construct: Workbook.PivotCaches method.
     
     
   • Description: Returns the PivotCaches collection representing all the Pivot Table caches within DestinationWorkbook.
     
     
3. Item: Create.
   
   
   • VBA Construct: PivotCaches.Create method.
     
     
   • Description: Creates a new PivotCache object representing the memory cache for the Pivot Table you create.
     
     
4. 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.
     
     
5. 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.
       
       
6. Item: createPivotTable
   
   
   • VBA Construct: PivotCache.CreatePivotTable method.
     
     
   • Description: Creates a Pivot Table based on the PivotCache created by the PivotCaches.Create method.
     
     
7. 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.
       
       
8. 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

1. 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

1. 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

1. 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

1. 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.
     
     
2. 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.
     
     
3. 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

1. Item: LastRow.
   
   
   • VBA Construct: Variable of the long data type.
     
     
   • Description: LastRow holds the number of the last row with data in SourceWorksheet.
     
     
2. Item: =.
   
   
   • VBA Construct: Assignment operator.
     
     
   • Description: Assigns the row number returned by the Range.Row property to the LastRow variable.
     
     
3. 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.
     
     
4. 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.
     
     
5. Item: LookIn:=xlFormulas.
   
   
   • VBA Construct: LookIn parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method looks in formulas (xlFormulas).
     
     
6. 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.
     
     
7. Item: SearchOrder:=xlByRows.
   
   
   • VBA Construct: SearchOrder parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method searches by rows (xlByRows).
     
     
8. 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).
     
     
9. 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

1. 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.
     
     
2. Item: =.
   
   
   • VBA Construct: Assignment operator.
     
     
   • Description: Assigns the column number returned by the Range.Column property to the LastColumn variable.
     
     
3. 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.
     
     
4. 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.
     
     
5. Item: LookIn:=xlFormulas.
   
   
   • VBA Construct: LookIn parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method looks in formulas (xlFormulas).
     
     
6. 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.
     
     
7. Item: SearchOrder:=xlByColumns.
   
   
   • VBA Construct: SearchOrder parameter of the Range.Find method.
     
     
   • Description: Specifies that the Range.Find method searches by columns (xlByColumns).
     
     
8. 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).
     
     
9. 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)

1. Item: SourceDataAddress.
   
   
   • VBA Construct: Variable of the String data type.
     
     
   • Description: SourceDataAddress holds the address of the cell range containing the source data.
     
     
2. Item: =.
   
   
   • VBA Construct: Assignment operator.
     
     
   • Description: Assigns the string returned by the Range.Address property to the SourceDataAddress variable.
     
     
3. 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).
     
     
4. 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.
     
     
5. 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.
     
     
6. 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.
     
     
7. 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”[=]

1. 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.
     
     
2. Item: PivotCaches
   
   
   • VBA Construct: Workbook.PivotCaches method.
     
     
   • Description: Returns the PivotCaches collection representing all the Pivot Table caches within Workbook.
     
     
3. Item: Create.
   
   
   • VBA Construct: PivotCaches.Create method.
     
     
   • Description: Creates a new PivotCache object representing the memory cache for the Pivot Table you create.
     
     
4. 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.
     
     
5. 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.
       
       
6. Item: createPivotTable
   
   
   • VBA Construct: PivotCache.CreatePivotTable method.
     
     
   • Description: Creates a Pivot Table based on the PivotCache created by the PivotCaches.Create method.
     
     
7. 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.
       
       
8. 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:

1. Identify or create the workbooks you work with:
   
   
   • Workbooks collection.
     
     
   • Workbooks.Add method.
     
     
   • Workbook object.
     
     
   • Application.Workbooks property.
     
     
   • Application.ActiveWorkbook property.
     
     
   • Application.ThisWorkbook property.
     
     
2. Identify or create the worksheets you work with:
   
   
   • Worksheets collection.
     
     
   • Worksheets.Add method.
     
     
   • Worksheet object.
     
     
   • Workbook.Worksheets property.
     
     
3. 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:
     
     
     • Range.Find method.
       
       
     • Range.Row property.
       
       
     • Range.Column property.
       
       
4. Obtain the names and addresses of the workbooks, worksheets and cell ranges you work with:
   
   
   • Workbook.Name property.
     
     
   • Worksheet.Name property.
     
     
   • Range.Address property.
     
     
5. Create a Pivot Table cache and Pivot Table report:
   
   
   • PivotCache object.
     
     
   • Workbook.PivotCaches method.
     
     
   • PivotCaches.Create method.
     
     
   • xlPivotTableSourceType enumeration.
     
     
   • PivotCache.CreatePivotTable method.
     
     
6. Work with Pivot Table fields:
   
   
   • PivotField object.
     
     
   • PivotTable.PivotFields method.
     
     
   • PivotField.Orientation property.
     
     
   • PivotField.Position property.
     
     
   • PivotField.Function property.
     
     
   • PivotField.NumberFormat property.
     
     
7. Concatenate strings:
   
   
   • Concatenation operators.
     
     
8. Work with variables and data types:
   
   
   • Dim statement.
     
     
   • Set statement.
     
     
   • = operator.
     
     
   • Data types:
     
     
     • Long data type.
       
       
     • String data type.
       
       
9. Simplify object references:
   
   
   • With… End With statement.