Excel Pivot Table Generation Macro

Pivot Tables are used in the financial environment to enable analysts to group and summarize statistics. My VBA macro to generate Pivot Tables from spreadsheets with numerous fields is a helpful tool. With a few clicks and drags the pivot table is generated from the active worksheet.

After starting the macro, the format of the spreadsheets is validated. When necessary adjustments to the format are recommended. The user receives the option to use the automatic feature when the format allows it.

To Generate a pivot table, the first row of the active spreadsheet must contain a unique non-blank name for each column. If it finds an empty cell or "EOF", the prompt to delete this column and all columns to the right is issued. You can cancel this macro and fix the cell or continue to delete that column and all columns to the right.

The SAS CSV macro is used to create a comma delimited file from a SAS library. When the input is a SAS PROC SUMMARY, the SAS CSV macro creates a file suitable to the automatic feature of this Macro. The user can always choose to specify the page, row, and data variables of the pivot table by clicking and dragging.

Upon starting the Excel Pivot Table Generator, a menu is created with the following three operations: Pivot Table Generator, No Save/Exit, and Save workbook.

The No Save/Exit option is to close this hidden workbook with the Pivot Table macros and remove the Pivot Table Generator menu.

The Save workbook option is to save and close the active workbook with the file type of Excel workbook-useful when the active workbook is a comma delimited CSV file. It uses a prompt to show the directory where the file will be saved with the option to cancel.

The format for the automatic feature is as follows. If the macro finds two adjoining cells in the first row that contain _TYPE_ and _FREQ_, the prompt to automatically select the pages and data columns of the spreadsheet is issued. All columns to the left of and including the column starting with _TYPE_ are considered PAGE variables, and all variables to the right are considered column variables. If auto column is selected, then the final prompt is to select the row variable. The row variable must be one of the page variables.

If the auto option is not selected, or the spreadsheet is not in the correct format, then a prompt with the following three sections are displayed: ROW, PAGEs, and COLUMNs. Only one row may be selected, but multiple pages and columns are available. PAGEs and COLUMNs can be selected with click and drag.

The first pivot table is created in the same workbook in a new spreadsheet named Pivot_table 1. Several pivot tables can be added to the same workbook.