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