We Value Your Privacy

We and our partners use technology such as cookies on our site to personalise content and ads, provide social media features, and analyse our traffic. Click below to consent to the use of this technology across the web. You can change your mind and change your consent choices at anytime by returning to this site.

Update Consent
Loading ...

How do I Create Pivot Table With VBA?

Updated July 20, 2017

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.

Loading ...
  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. Click the "Developer" tab and click "Visual Basic" to launch the VB Editor. Type the following code to create a new VBA sub procedure:

  3. Private Sub createPivotTable()

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

  5. Define the worksheets you will use:

  6. Determine the row and column count with data:

  7. Define the range for your data:

  8. Create the pivot table in "Sheet2:"

  9. Define the source data for the Pivot table:

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

Loading ...

Things You'll Need

  • Microsoft Office Excel

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.

Loading ...