How can I simplify my pivot tables

[marshybid]

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

[Bob Phillips]

Completely untested

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                                   SourceData:="'Approved Timesheets'!A:AU").CreatePivotTable _
                                   TableDestination:="'Approved Timesheets Pivot'!R3C1", _
                                   TableName:="PivotTable1", _
                                   DefaultVersion:=xlPivotTableVersion10
    With ActiveSheet.PivotTables("PivotTable1")
    PTSubtotals .PivotFields("Order ID")
    PTSubtotals .PivotFields("Timesheet ID")
    PTSubtotals .PivotFields("X-Ref PO ID")
    PTSubtotals .PivotFields("Cost Center")
    PTSubtotals .PivotFields("Contingent Staff First Name")
    PTSubtotals .PivotFields("Contingent Staff Last Name")
    PTSubtotals .PivotFields("Standard Rate")
    PTSubtotals .PivotFields("Overtime Rate")
    PTSubtotals .PivotFields("Second Overtime Rate")
    PTSubtotals .PivotFields("Regular Hours")
    PTSubtotals .PivotFields("Total Overtime Hours")
    PTSubtotals .PivotFields("Total Second Overtime Hours")
    PTSubtotals .PivotFields("Timesheet For Week Ending")
    PTSubtotals .PivotFields("Total Amount")
    .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")
    .PivotFields("Timesheet ID").Orientation = xlDataField
    End With
    
    With Sheets("Approved Timesheets Pivot")
    With .Rows("4:4")
        .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").ColumnWidth = 9.71
    .Columns("F:F").ColumnWidth = 9.43
    .Range("I:I,K:K,M:M,N:N").NumberFormat = _
    "_-[$?-809]* #,##0.00_-;-[$?-809]* #,##0.00_-;_-[$?-809]* ""-""??_-;_-@_-"
    .Columns("A:A").ColumnWidth = 10.57
    .Columns("B:B").ColumnWidth = 10.57
    .Columns("O:O").Hidden = True
    End With
End Sub

Private Function PTSubtotals(ByRef PTField As PivotField)
PTField.Subtotals = _
            Array(False, False, False, False, False, False, False, False, False, False, False, False)
End Function

[Bob Phillips]

You can extend this if all your pivots are the same by parameterising the sheets all within one procedure.

[marshybid]

You are a legend

xld, this worked a treat.

I have amended slightly, took your advice

You can extend this if all your pivots are the same by parameterising the sheets all within one procedure.

and that has also worked.

thank you so much.

I will mark this thread as solved.

Marshybid