ive started out making a pivot table. it creates ok and i get what i need expect set of data is missing
the following code is with recording, but i have recoded this into structured VBA with objects etc, to make it more dynamic. see under
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"InsideMarketData!R1C1:R16384C51").CreatePivotTable TableDestination:= _
"[pivots.xls]Sheet38!R3C1", TableName:="PivotTable2", DefaultVersion:= _
xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Trader Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Member Name")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Order ID"), "Count of Order ID", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("T or F")
.Orientation = xlColumnField
.Position = 1
End With
Range("J9").Select
End Sub
this is the code i've made with the help of some on here. but with the Order ID part i cannot get what i desire...i dont know how to add
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Order ID"), "Count of Order ID", xlCount
into this:
'== set PivotTable Location and Create ==
Set PT = PTCache.CreatePivotTable(TableDestination:=Cells(3, 1), _
TableName:="Trader Compare", DefaultVersion:=xlPivotTableVersion10)
'== assign fields into rows / columns ==
PT.ManualUpdate = False
'== position means columns across PT _
such as position 1 means A _
position 2 means B _
and so on.
'== add pivot table fields == goddam stupid moronic way of doing things
With PT.PivotFields("Trader Name")
.Orientation = xlRowField
.Position = 1
End With
With PT.PivotFields("Member Name")
.Orientation = xlRowField
.Position = 2
End With
With PT.PivotFields("Order ID")
End With
With PT.PivotFields("T or F")
.Orientation = xlColumnField
.Position = 1
End With
'== get rid of totals in Trader Name ==
With PT.PivotFields("Trader Name")
.Subtotals = Array(False, False, False, False, _
False, False, False, False, _
False, False, False, False)
End With
grrr! thanks and sorry about all the code!!
Bookmarks