Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 47 Automate PivotTable Creation

The steps you need to follow to create a
PivotTable require some effort, and that effort often is redundant.
With a small bit of VBA, you can create simple PivotTables
automatically.

PivotTables are a very clever and
potent feature to use on data that is stored in either a list or a
table. Unfortunately, the mere thought of creating a PivotTable is
enough to prevent some people from even experimenting with them.
Although some PivotTable setups can get very complicated, you can
create most PivotTables easily and quickly. Two of the most commonly
asked questions in Excel concern how to get a count of all items in a
list, and how to create a list of unique items from a list that
contains many duplicates. In this section, we'll
show you how to create a PivotTable quickly and easily that
accomplishes these tasks.

Assume you have a long
list of names in column A, with cell A1 as your heading, and you want
to know how many items are on the list, as well as generate a list of
unique items. Select cell A1 (your heading) and then select Data
PivotTable and PivotChart Report (or Data
PivotTable Report on Macs) to start the PivotTable Wizard.

Make sure that either Microsoft Excel List
or Database is selected, or that you have selected a single cell
within your data. This will allow Excel to automatically detect the
underlying data it is to use next. If you're using a
Windows PC, select PivotTable under "What kind of
report do you want to create?" (This question
isn't asked on Macintoshes.) Click the Next button.
The PivotTable Wizard should automatically have picked up the correct
range for your data in column A and will highlight it in your sheet.
If it is highlighted, click the Next button. Otherwise, use your
mouse to select the range. Click the Layout button and drag to the
Data area what will be your only fieldyou should see your
title as it appears in cell A1 floating about. Drag the field again,
this time into the Row area. Your screen should look something like
Figure 4-4. Click OK.

At this stage, if you want you can double-click the Field button in the Data area (this is labeled Count of Names in Figure 4-3) and change the Summarize by: option to a function of your choicee.g., Sum, Average, etc. Excel will by default use the COUNT function if it's working with text and use the SUM function if it's working with numbers.

Figure 4-4. PivotTable Field and PivotTable Layout dialogs

Finally, select New Worksheet as the
destination of your PivotTable Report and click the Finish button.
You should see your PivotTable on a new worksheet containing the
unique items from your list along with a count of how many times each
item (name) appears in your list.

What if you want to have a macro perform
all those steps for you, creating a PivotTable from any column you
feed it? If you simply record a macro, you'll find
it often works only if your data has the same heading. To avoid this,
you can create a simple macro stored in your workbook or in your
personal macro workbook (described in Chapter 7) that you can use to create a PivotTable on
any list of items. This requires that you write some generic VBA code
and enter it into a standard module in your personal macro workbook
or in any other workbook.

To
start, select Tools Macro Visual Basic Editor
(Alt/Option-F11) and then select Insert Module. Enter the
following code:

Sub GetCount( )
Dim Pt As PivotTable
Dim strField As String
strField = Selection.Cells(1,1).Text
Range(Selection, Selection.End(xlDown)).Name = "Items"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="=Items").CreatePivotTable TableDestination:=", _
TableName:="ItemList"
Set Pt = ActiveSheet.PivotTables("ItemList")
ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1)
Pt.AddFields RowFields:=strField
Pt.PivotFields(strField).Orientation = xlDataField
End Sub

To return to Excel, close the Script window, or press
Alt/-Q, and save your workbook.

Before running this code, select the
heading of your list and ensure that your list contains no blank
cells.

Sorting your list will remove blank cells quickly.

The code will automatically create a named
range of your list, called Items. It will then create the PivotTable
based on this named range on a new worksheet.

The next time you have a long list of data, you can simply select its
heading and run this macro. All the PivotTable setup work will be
done in the blink of an eye.