Insert header in pivottitles

[AnnetteTR]

Hi
I am making a quite large macro to create a pivot table where the user selects the items to sort and make a sum of these:

Sub OutputPrUnit()
'
'
'

Dim Pt As PivotTable
Dim Sortering As String
Dim Forstevalg As String
Dim Andetvalg As String

'Change to the sheet with data
Sheets("Data Ventilation output trial").Select

'move to top left cell
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select

' Name DataArea
Range(Selection.End(xlToRight), Selection.End(xlDown)).Name = "Items"

' Get choise from user
frmOutput.Show
' Three values from user
Sortering = frmOutput.cbxSortering.Text
Forstevalg = frmOutput.cbx1Vis.Text
Andetvalg = frmOutput.cbx2Vis.Text

' Create Pivot Caches
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="Items").CreatePivotTable tabledestination:="", TableName:="ItemList"

Set Pt = ActiveSheet.PivotTables("ItemList")
ActiveSheet.PivotTableWizard tabledestination:=Cells(3, 1)
Pt.AddFields RowFields:=Sortering 'Move the list heading to the row field
Pt.PivotFields(Sortering).Orientation = xlDataField ' Move the list heading to the data field
With Pt.PivotFields(Sortering)
.Orientation = xlRowField
.Position = 1
End With

Pt.AddDataField Pt.PivotFields(Forstevalg), "Sum of Forstevalg", xlSum
Pt.AddDataField Pt.PivotFields(Andetvalg), "Sum of Andetvalg", xlSum
End Sub

The makro works well, but the title of the Columns are "Sum of Forstevalg" and "Sum of Andetvalg" .
Instead of Forstevalg it should have been the value of the string Forstevalg, and the same for Andetvalg (The value the user have chosen)

What is the syntax for that?

Regards Annette

 

[RoryA]

You need:

Pt.AddDataField Pt.PivotFields(Forstevalg), "Sum of " & Forstevalg, xlSum
Pt.AddDataField Pt.PivotFields(Andetvalg), "Sum of " & Andetvalg, xlSum

 

[AnnetteTR]

Hi rorya
Thankyou - that did the trick.
/ Annette