PDA

View Full Version : [SOLVED] How can I simplify my pivot tables



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

Bob Phillips
04-29-2008, 05:44 AM
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
04-29-2008, 05:45 AM
You can extend this if all your pivots are the same by parameterising the sheets all within one procedure.

marshybid
04-29-2008, 08:40 AM
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