Figuring the Size of a Finished Pivot Table

It is difficult to know the size of a pivot table in advance. If you run a report of transactional data on one day, you may or may not have sales from the West region. This could cause your table to either be 5 or 6 columns wide. Use the special property TableRange2 to refer to the entire resultant pivot table.

Because of the limitation of a pivot table, I generally copy the results of a pivot table to a new location on the worksheet and then delete the original pivot table. The code shown in CreateSummaryReportUsingPivot() creates a small pivot table. Note that you can set the ColumnGrand and RowGrand properties of the table to False to prevent the totals from being added to the table.

PT.TableRange2 includes the entire pivot table. In this case, this includes the extra row at the top with the button Sum of Revenue. To eliminate that row, the code copies PT.TableRange2, but offsets this selection by one row by using .Offset(l, 0). Depending on the nature of your pivot table, you might need to use an offset of 2 or more rows to get rid of extraneous information at the top of the pivot table.

The code copies PT.TableRange2 and does a PasteSpecial to cell J10. At that point in the code, your worksheet appears like Figure 12.8. The table in J2 is a live pivot table and the table in J10 is just the copied results.

You can then totally eliminate the pivot table by applying the Clear method to the entire table. If your code is then going on to do additional formatting, you should remove the PivotCache from memory by setting PTCache equal to Nothing:

Sub CreateSummaryReportUsingPivot()

1 Use a Pivot Table to create a static summary report 1 with Region going down the rows and products across

Dim WSD As Worksheet

Dim PTCache As PivotCache

Dim PT As PivotTable

Dim PRange As Range

Set WSD = Worksheets("Pivot Table")

' Delete any prior pivot tables For Each PT In WSD.PivotTables

PT.TableRange2.Clear Next PT

' Define input area and set up a Pivot Cache FinalRow = WSD.Cells(65536, 1).End(xlUp).Row Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=PRange.Address)

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("J2"), _

TableName:="PivotTable1") PT.ManualUpdate = True ' Set up the row fields

PT.AddFields RowFields:="Region", ColumnFields:="Product"

' Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 End With

0 0

Post a comment