Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
VBA code to initialize a PivotTable
'SetupPivot: The following subroutine sets up a pivot table
' author:
'I received assitance from the following site:
Sub SetupPivot()
Dim pt As PivotTable
Dim WSD As Worksheet
Set WSD = Worksheets("DATA")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("PIVOT")
Dim PTCache As PivotCache
Dim PRange As Range
'Clean up any pivot tables on the PIVOT page
For Each pt In PTOutput.PivotTables
Next pt
' Find the last row with data; Use helper function
Dim finalRoaw As Long
finalRow = findFinalRow("DATA")
' Find the last column with data
Dim finalCol As Long
finalCol = findLastColumn("DATA")
' Select the range of the data
Set PRange = WSD.Range(Cells(1, 1), Cells(finalRow, finalCol))
PRange.Name = "IEDData"
'Set the PivotTable Cache to match the arange
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="IEDData", Version:=xlPivotTableVersion12)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
' Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True
' Set up the row fields --
' if we don't we'll have a hard time finding pivot table later
pt.AddFields RowFields:=Array("MND")
' Now calc the pivot table
pt.ManualUpdate = False
'Delete the summary page
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment