Programming a pivot
 

I receive a mass of data from an external program, which I paste into
Excel and then run a macro against to clean up and create a couple of
pivots out of. The problem I am having is that when I create my
pivots, the data is lined up in rows under the column fields, instead
of in columns alongside them. I have cobbled together a "move" of the
data to get it into the correct format, but this is not a very elegant
solution and, with the volumes increasing, not practical long term.
Can anyone point out where I am going wrong as I have tried changing
the "Orientation = xlDataField" to "Orientation = xlColumnField" to no
avail.

the data format is as follows
Column
A Proj#
B Lvl1
C Lvl2
D Lvl3
E Lvl4
F Region
G Lvl6
H Region/Country
I Business or IT
J Project
K Phase/Task
L Resource
M Team
N Team Leader
O Director
P-AA Monthly (Jan-Dec) forecast of requirements in days

the code I am using at present to create the Pivot is as follows

Sheets("Project Data").Select
EndData = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Forecast by Team").Visible = True
Sheets("Forecast by Team").Select
Columns("A:AE").Select
Selection.Delete Shift:=xlToLeft
wbname = ActiveWorkbook.Name

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"'Project data'!R1C1:R" & EndData & "C27").CreatePivotTable
TableDestination:= _
"'[" & wbname & "]Forecast by Team'!R1C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable1").PivotFields
("WorkID").Subtotals = Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Phase/
Task").Subtotals = _
Array(False, False, False, False, False, False, False, False,
False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields
("Resource").Subtotals = _
Array(False, False, False, False, False, False, False, False,
False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Team
Leader").Subtotals = _
Array(False, False, False, False, False, False, False, False,
False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields
("Director").Subtotals = _
Array(False, False, False, False, False, False, False, False,
False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array
("Director", _
"Team Leader", "Team", "Project", "Phase/Task", "Resource",
"WorkID", "Data")

For M = 1 To 12
With ActiveSheet.PivotTables("PivotTable1").PivotFields
(ColMonth(M))
.Orientation = xlDataField
.Caption = ColMonth(M) & " "
.Position = M
.NumberFormat = "0.00"
End With
Next

ActiveWorkbook.ShowPivotTableFieldList = True
Range("H1").Select
With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With
ActiveWorkbook.ShowPivotTableFieldList = False

Any help much appreciated
Thanks and regards
Fred