Hi all,
I am using Excel 2007 and I've created a macro which allows one to create a Pivot Table from a macro. One thing that I'm stuck on is instead of having the user of the macro hand enter the variables, I would like them to be able to use a list (or combo) box in a userform to select the variable/field, which will then populate the macro and create the pivot table when they click a button. The other thing that I can't figure out is how to get the macro to automatically create a new sheet rather than manually changed the worksheet (as you can see below I am at Sheet13. Thanks so much!
Sub MakePivotTable()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Sheet1")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Sheet13")
Dim PTCache As PivotCache
Dim PRange As Range
' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="SamplePivot")
' 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
pt.AddFields RowFields:=Array( _
"class")
' Set up the data fields
With pt.PivotFields("dorm")
.Orientation = xlDataField
.Function = xlCount
.Calculation = xlPercentOfTotal
.NumberFormat = "0.00%"
.Position = 1
End With
' Now calc the pivot table
pt.ManualUpdate = False
End Sub
Bookmarks