Value field appearing above row labels pivot table

[Pogla]

Thanks in advance for you help.

This seems like a simple question, but I can't find the answer

I'm trying to create a pivot table based on a separate data sheet.

I modified the code I got from recording a macro, the problem is, however, that the Count field in the values is appearing above the row labels, rather than the right most column.

Code I have so far:

[vba] Dim PT As PivotTable, strField As String, WSD As Worksheet, PTOutput As Worksheet, PTCache As PivotCache, _
PRange As Range, finalCol As Long, finalRow As Long

'source and destination worksheets
Set WSD = Worksheets("Output")
Set PTOutput = Worksheets("PivotTable")

PTOutput.Select
'last row of data
finalRow = Worksheets("Output").Range("A65536").End(xlUp).Offset(1, 0).Row

finalRow = finalRow - 1


'column list
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data

'create definition of pivot
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

'clear existing pivot
For Each PT In PTOutput.PivotTables
PT.TableRange2.Clear
Next PT

' Create the pivot table
Set PT = PTCache.CreatePivotTable(TableDestination:=PTOutput.Range("A2"), _
TableName:="SummaryPivot")


' Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
PT.ManualUpdate = True
' Set up the row fields

'tabular layout
PT.RowAxisLayout xlTabularRow


With PT.PivotFields("Receipt_Nbr")
.Orientation = xlRowField
.Position = 1
End With
PT.PivotFields("Receipt_Nbr").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
[/vba]

(SNIP: define lots more rows )

[vba] With PT.PivotFields("Dwelling Accepted")
.Orientation = xlRowField
.Position = 15
End With
PT.PivotFields("Dwelling Accepted"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)

PT.AddDataField PT.PivotFields("Total_No_Dwellings"), "Count of Total_No_Dwellings" _
, xlCount

PT.TableStyle2 = "PivotStyleDark7"
PT.ShowTableStyleRowStripes = True
PT.ShowTableStyleColumnStripes = True

' Now calc the pivot table
PT.ManualUpdate = False[/vba]

The Total_No_Dwellings is the field I want to appear to the right, but it is appearing at the top.

[joms]

I'm not sure whether i'm right on this, but I look at your code you have this one:
[VBA]
With PT.PivotFields("Receipt_Nbr")
.Orientation = xlRowField
.Position = 1
End With

do you think the xlRowField? should be changed to xlColumnField?


[/VBA]

or check out this link below:
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

[p45cal]

Also check out the section starting "This layout makes it difficult to compare the Units" (it's about half way down) at this website: http://www.contextures.com/xlPivot02.html