MS Excel / VBA help thread

This is really pissing me off, it seems so trivial and yet it's been annoying me all morning.

I have a macro that runs through a load of files and creates a pivot table in each. Files are identical in format.

Everything is fine except for one point.

The values that I am summing, the date fields, are appearing stacked as rows rather than as a column for each one.

Basically like this http://www.mrexcel.com/forum/excel-questions/476975-visual-basic-applications-pivot-table-datafields-horizontal-vertical.html

However, because I'm generating lots of pivot tables the code in the solution there doesn't work (it's not always pivot table 1). I've tried a whole variety of solutions and just get constant errors.

Grateful for any help if I'm missing something obvious

This is my code so far:

Sub testpivot()

Dim objTable As PivotTable, objField As PivotField

' Select the sheet and first cell of the table that contains the data.

' ActiveWorkbook.Sheets("Employees").Select

Range("A1").Select

' Create the PivotTable object based on the Employee data on Sheet1.
Set objTable = Sheet1.PivotTableWizard

' Specify row and column fields.
Set objField = objTable.PivotFields("Customer")
objField.Orientation = xlRowField
   objField.Position = 1
Set objField = objTable.PivotFields("Product")
objField.Orientation = xlRowField
   objField.Position = 2
Set objField = objTable.PivotFields("Prod Descr")
objField.Orientation = xlRowField
   objField.Position = 3
Set objField = objTable.PivotFields("Character")
objField.Orientation = xlRowField
   objField.Position = 4
Set objField = objTable.PivotFields("BillT")
objField.Orientation = xlRowField
   objField.Position = 5

' Format as tabular
 objTable.RowAxisLayout xlTabularRow

 With objTable

For Each objField In .PivotFields
' pvtFld.Subtotals(1) = True
objField.Subtotals(1) = False
Next objField
End With

With objTable
For Each objField In .DataPivotFields

objField.Orientation = xlcolumfield
Next objField
End With

' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("Sales UOM")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##0"




Set objField = objTable.PivotFields("  Gross Sls")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##0"

End Sub