PivotTables are one of Microsoft Excel's most important features. They permit to review, sort and control large, complex datasets in way that makes them much simply to examine. If discover yourself creating large number of pivot tables or creating them regularly, you may capable to save time by writing Visual Basic for Applications script that can create them mechanically.

Instructions

• 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 are previous version of Excel, click "Tools" menu, go to "Macro" and click "Macros." This carry up Macro dialog box.

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

• Insert code to retrieve reference to worksheet: Set worksheet = ThisWorkbook.Worksheets (1). If data is not on first worksheet in workbook, alter number above from 1 to index of worksheet that holds data.

• Find variety of data to be worn in pivot table:
put range = worksheet.UsedRange

• build Pivot bench entity: spreadsheet.PivotTableWizard