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
You can’t perform that action at this time.