MS Excel Tutorials - Build Pivot Tables By Using VBA
Version 5 of Excel introduced a powerful new macro language called Visual Basic for Application (VBA). Every copy of Excel shipped since 1993 has had a copy of the powerful VBA language hiding behind the worksheets.
Enable VBA in Your Copy of Excel
If you are using Excel 2000 or newer, VBA may be disabled. Before you can start using VBA, you need to enable macros on the Security dialog box. From the application menu, choose Tools, Macro, Security. Set the macro security level to medium. This will allow VBA macros to run, but you will have to explicitly enable them when you open Excel.
Table of content (tutorial index)
advertisement
Enable VBA in Your Copy of Excel
Visual Basic Tools
Understanding Object-Oriented Code
End+Down Versus End+Up
Use Super-VariablesObject Variables
Build a Pivot Table in Excel VBA
Getting a Sum Instead of a Count
Size of a Finished Pivot Table
Revenue by Model for a Product Line Manager
Default Number Format
Suppress Subtotals for Multiple Row Fields
Handle Additional Annoyances
New Workbook to Hold the Report
Summary on a Blank Report Worksheet
Final Formatting
Add Subtotals
Put It All Together
Issues with Two or More Data Fields
Calculated Data Fields
Calculated Items
Summarize Date Fields with Grouping
Group by Week
AutoShow Feature to Produce Executive Overviews
ShowDetail to Filter a Recordset
Create Reports for Each Region or Model
Manually Filter Two or More Items in a PivotField
Control the Sort Order Manually
Sum, Average, Count, Min, Max, and More
Report Percentages
Percentage Growth from Previous Month
Running Total
Special Considerations for Excel 97