Microsoft Office Tutorials and References
In Depth Information
Creating a Reverse Pivot Table
When the user clicks the OK button in the UserForm, VBA code validates the ranges and then
calls the ReversePivot procedure with this statement:
Call ReversePivot(SummaryTable, OutputRange, cbCreateTable)
It passes three arguments:
h SummaryTable : A Range object that represents the summary table.
h OutputRange : A Range object that represents the upper-left cell of the output range.
h cbCreateTable : The Checkbox object on the UserForm.
This procedure will work for any size summary table. The number of data rows in the output table
will be equal to (r-1) * (c-1) , where r and c represent the number of rows and columns in
the SummaryTable.
The code for the ReversePivot procedure follows:
Sub ReversePivot(SummaryTable As Range, _
OutputRange As Range, CreateTable As Boolean)
Dim r As Long, c As Long
Dim OutRow As Long, OutCol As Long
‘ Convert the range
OutRow = 2
Application.ScreenUpdating = False
OutputRange.Range(“A1:C3”) = Array(“Column1”, “Column2”, “Column3”)
For r = 2 To SummaryTable.Rows.Count
For c = 2 To SummaryTable.Columns.Count
OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
OutRow = OutRow + 1
Next c
Next r
‘ Make it a table?
If CreateTable Then _
ActiveSheet.ListObjects.Add xlSrcRange, _
OutputRange.CurrentRegion, , xlYes
End Sub
The procedure is fairly simple. The code loops through the rows and columns in the input range
and then writes the data to the output range. The output range will always have three columns.
The OutRow variable keeps track of the current row in the output range. Finally, if the user
checked the check box, the output range is converted to a table by using the Add method of the
ListObjects collection.
Search JabSto ::

Custom Search