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:
Other People Are Reading
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.
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.
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.
Determine the range of the data to be used in the pivot table:
Set range = worksheet.UsedRange
Create the PivotTable object:
worksheet.PivotTableWizard(SourceType := xlDatabase, SourceData := range, TableDestination := worksheet.Range("B5"))
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for