marshybid
04-29-2008, 05:05 AM
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 :help
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 :help