Pogla
07-10-2013, 07:16 PM
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:
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)
(SNIP: define lots more rows )
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
The Total_No_Dwellings is the field I want to appear to the right, but it is appearing at the top.
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:
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)
(SNIP: define lots more rows )
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
The Total_No_Dwellings is the field I want to appear to the right, but it is appearing at the top.