Hi All,

I'm pretty new to VB and would appreciate any suggestions on how to simplify Pivot Table creation (I'm using excel 2003)

An example of a current PT in my macro is:

 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "'Approved Timesheets'!A:AU").CreatePivotTable _
    TableDestination:="'Approved Timesheets Pivot'!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion10
    Sheets("Approved Timesheets Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Order ID")
        .Subtotals = Array(False, False, False, False, False, False, False, False, False, _
        False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Timesheet ID").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("X-Ref PO ID").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Center").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Contingent Staff First Name").Subtotals = Array(False, False, False, False, False, _
        False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Contingent Staff Last Name" _
        ).Subtotals = Array(False, False, False, False, False, False, False, False, False, False _
        , False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Standard Rate").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Overtime Rate").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Second Overtime Rate"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Regular Hours").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Total Overtime Hours"). _
        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
        False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Total Second Overtime Hours").Subtotals = Array(False, False, False, False, False, _
        False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Timesheet For Week Ending").Subtotals = Array(False, False, False, False, False, _
        False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Total Amount").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Order ID", _
        "X-Ref PO ID", "Cost Center", "Contingent Staff First Name", _
        "Contingent Staff Last Name", "Timesheet ID", "Timesheet For Week Ending", "Regular Hours", "Standard Rate", _
        "Total Overtime Hours", "Overtime Rate", "Total Second Overtime Hours", _
        "Second Overtime Rate", "Total Amount")
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Timesheet ID").Orientation _
        = xlDataField
    End With
    Sheets("Approved Timesheets Pivot").Select
    Rows("4:4").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("D:D,E:E").Select
    Range("E1").Activate
    Selection.ColumnWidth = 9.71
    Columns("F:F").ColumnWidth = 9.43
    Range("I:I,K:K,M:M,N:N").Select
    Range("N1").Activate
    Selection.NumberFormat = _
        "_-[$?-809]* #,##0.00_-;-[$?-809]* #,##0.00_-;_-[$?-809]* ""-""??_-;_-@_-"
    Range("A1").Select
    Columns("A:A").ColumnWidth = 10.57
    Columns("B:B").ColumnWidth = 10.57
    Columns("O:O").Select
    Selection.EntireColumn.Hidden = True
    Range("A1").Select
This is basically taken from recording a macro to create the PT then minor editing.

I would like to be able to make all Pivot Fields SubTotals (False) without having to repeat for each one, also remove any "blank" pivot items from the first pivot field.

I'm sure that there must be a simpler way.

Can anyone help.

Thanks,

Marshybid