Macro 77: Transpose Entire Data Range with a PivotTable
You may often encounter matrix-style data tables like the one shown in Figure 6-6. The problem is
that the month headings are spread across the top of the table, pulling double duty as column labels
and actual data values. In a PivotTable, this format would force you to manage and maintain 12
fields, each representing a different month.
Ideally, the data would be formatted in a more tabular format, as shown in Figure 6-7.
Figure 6-6: Matrix-style reports are often problematic in PivotTables.
Figure 6-7: Tabular data sets are ideal when working with data.
There are countless methods you can use to transpose an entire data range. The macro in this section
provides an easy way to automate this task.
Because of this, you can only have one dimension column. To understand this, take a look at
Figure 6-6. Note that the first column is essentially a concatenated column con- sisting of two
data dimensions: Market and Category. This is because a multiple consoli- dation range pivot
table can handle only one dimension field.
How it works
You can transpose a dataset with a multiple consolidation range PivotTable. The manual steps to do
so are
1. Press Alt+D+P to call up the Excel 2003 PivotTable Wizard.
2. Click the option for Multiple Consolidation Ranges, and then click Next.
3. Select the I Will Create the Page Fields option, and then click Next.
4. Define the range you are working with and click Finish to create the PivotTable.
5. Double-click on the intersection of the Grand Total row and column.
This macro duplicates the steps above, allowing you to transpose your data set in a fraction of the
time.
Sub Macro77()
‘Step 1: Declare your Variables
Dim SourceRange As Range
Dim GrandRowRange As Range
Dim GrandColumnRange As Range
‘Step 2: Define your data source range
Set SourceRange = Sheets(“Sheet1”).Range(“A4:M87”)
‘Step 3: Build Multiple Consolidation Range Pivot Table
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation,
_ SourceData:=SourceRange.Address(ReferenceStyle:=xlR1C1), _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=””, _
TableName:=”Pvt2″, _
DefaultVersion:=xlPivotTableVersion14
‘Step 4: Find the Column and Row Grand Totals
ActiveSheet.PivotTables(1).PivotSelect “‘Row Grand Total'”
Set GrandRowRange = Range(Selection.Address)
ActiveSheet.PivotTables(1).PivotSelect “‘Column Grand
Total'” Set GrandColumnRange = Range(Selection.Address)
‘Step 5: Drill into the intersection of Row and Column
Intersect(GrandRowRange, GrandColumnRange).ShowDetail =
True
End Sub
How to use it
You can implement this kind of a macro in a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
