How to Create a Pivot Table Using Excel VBA

Written by samuel porter
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Create a Pivot Table Using Excel VBA
Excel VBA can create pivot tables. (data image by jeancliclac from

PivotTables are one of Microsoft Excel's most powerful features. They allow you to summarise, sort and manipulate large, complex datasets in a way that makes them much easier to analyse. If you find yourself creating a large number of pivot tables or creating them frequently, you may be able to save time by writing a Visual Basic for Applications script that can create them automatically.

Skill level:


  1. 1

    Create a new Excel spreadsheet. If you're using Microsoft Excel 2007 or later, go to the "View" tab on the ribbon and click "Macros;" if you're using an earlier version of Excel, click the "Tools" menu, go to "Macro" and click "Macros." This will bring up the Macro dialogue box.

  2. 2

    Type in a new macro name and click "Create." You should see the Visual Basic for Applications editor appear with a new subroutine bearing the name that you chose.

  3. 3

    Add code to retrieve a reference to your worksheet:

    Set worksheet = ThisWorkbook.Worksheets(1)

    If your data is not on the first worksheet in the workbook, change the number above from 1 to the index of the worksheet that holds your data.

  4. 4

    Determine the range of the data to be used in the pivot table:

    Set range = worksheet.UsedRange

  5. 5

    Create the PivotTable object:

    worksheet.PivotTableWizard(SourceType := xlDatabase, SourceData := range, TableDestination := worksheet.Range("B5"))

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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