Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
VBA code to initialize a PivotTable
'SetupPivot: The following subroutine sets up a pivot table
'
'
' author: steven.henderson@usma.edu
'
'I received assitance from the following site:
'
'http://www.zerrtech.com/content/pivot-table-creation-using-excel-vba
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
pt.TableRange2.Clear
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
Sheets("DATA").Select
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), _
TableName:="IEDPivot")
' 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
Sheets("SUMMARY").Select
Sheets("SUMMARY").Range("A1:Z300").Clear
Sheets("CONTROL").Select
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.