How do I Create Pivot Table With VBA?

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How do I Create Pivot Table With VBA?
Create a VBA sub procedure in Excel. (Stockbyte/Stockbyte/Getty Images)

Creating pivot tables in Excel using Visual Basic for Applications (VBA) can save you time due to your not having to create the tables manually. VBA is a computer programming language used in Microsoft Office applications to automate routine tasks such as creating a pivot table. Don't spend your time creating pivot tables manually if you have many sheets in your workbook; instead create a sub procedure to create them dynamically.

Skill level:
Moderate

Other People Are Reading

Things you need

  • Microsoft Office Excel

Show MoreHide

Instructions

  1. 1

    Launch Microsoft Office Excel and type "Customer" in "A1," "Item" in "B1," and "Qty" in "C1." Type "Jon" in "A2," "Jon" in "A3," "Clara" in "A4," "Clara" in "A5," and "Clara" in "A6." Type "soda" in "B2," "beer" in "B3," "soda" in "B4," "water" in "B5," and "beer" in "A6." Type "2" in "C2," "5" in "C3," "10" in "C4," "12" in "C5," and "15" in "C6."

  2. 2

    Click the "Developer" tab and click "Visual Basic" to launch the VB Editor. Type the following code to create a new VBA sub procedure:

    Private Sub createPivotTable()

  3. 3

    Create the variables you will use to create the pivot table by adding the following code:

    Dim pt As PivotTable
    
    Dim wrkSht As Worksheet
    
    Dim pvtSht As Worksheet
    
    Dim PTCache As PivotCache
    
    Dim PRange As Range
    
    Dim final Row As Long
    
    Dim final Col As Long
    
  4. 4

    Define the worksheets you will use:

    Set wrkSht = Worksheets("Sheet1")
    
    Set pvtSht = Worksheets("Sheet2")
    
  5. 5

    Determine the row and column count with data:

    final Row = wrkSht.Cells(Application.Rows.Count, 1).End(xlUp).Row
    
    final Col = wrkSht.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    
  6. 6

    Define the range for your data:

    Set PRange = wrkSht.Cells(1, 1).Resize(final Row, final Col)
    
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
    
  7. 7

    Create the pivot table in "Sheet2:"

    Set pt = PTCache.createPivotTable(TableDestination:=pvtSht.Cells(1, 1), _
    
    TableName:="SamplePivot")
    
    pt.ManualUpdate = True
    
  8. 8

    Define the source data for the Pivot table:

    pt.AddFields RowFields:=Array("Item")
    
    With pt.PivotFields("Qty")
    
        .Orientation = xlDataField
    
        .Function = xlSum
    
        .Position = 1
    
    End With
    
    pt.ManualUpdate = False
    
  9. 9

    Finish your sub by typing "End Sub" in the end of your procedure. Press "F5" to run the procedure and create the pivot table using VBA.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.