Pivot Table Calculated Datafield Caption Problem
 

Hello, I'm having a problem with Excel forcing "Sum of " in front of the
Caption I'm assigning to my Datafields in a Pivot Table. Please see code
below. In this case Excel takes my Caption "Backlog Coverage, Rev (K$)" and
turns it into "Sum of Backlog Coverage, Rev (K$)" on the report. On the
report I don't want the users to see that "Sum of ." Thanks in advance for
any help you can provide.
Best Regards,
Dean

CODE EXCERPT

Set ptDashPORTPivot = Worksheets("DashPORT").PivotTables("DashPORTPivot" )

strDatafieldCaption = "Backlog Coverage, Rev (K$)"
strDatafieldFormula = "(BillGrossR + BLCSDR)/1000"
strNumberFormat = "#,###;-#,###;"

ptDashPORTPivot.CalculatedFields.Add strDatafieldCaption,
strDatafieldFormula, True

With ptDashPORTPivot.PivotFields(strDatafieldCaption)
.Orientation = xlDataField
.NumberFormat = strNumberFormat
End With

Pivot Table Calculated Datafield Caption Problem
 

You can add a space to the end of the caption:

With ptDashPORTPivot.PivotFields(strDatafieldCaption)
.Orientation = xlDataField
.NumberFormat = strNumberFormat
.Caption = strDatafieldCaption & " "
End With


SaeOngJeeMa wrote:
Hello, I'm having a problem with Excel forcing "Sum of " in front of the
Caption I'm assigning to my Datafields in a Pivot Table. Please see code
below. In this case Excel takes my Caption "Backlog Coverage, Rev (K$)" and
turns it into "Sum of Backlog Coverage, Rev (K$)" on the report. On the
report I don't want the users to see that "Sum of ." Thanks in advance for
any help you can provide.
Best Regards,
Dean

CODE EXCERPT

Set ptDashPORTPivot = Worksheets("DashPORT").PivotTables("DashPORTPivot" )

strDatafieldCaption = "Backlog Coverage, Rev (K$)"
strDatafieldFormula = "(BillGrossR + BLCSDR)/1000"
strNumberFormat = "#,###;-#,###;"

ptDashPORTPivot.CalculatedFields.Add strDatafieldCaption,
strDatafieldFormula, True

With ptDashPORTPivot.PivotFields(strDatafieldCaption)
.Orientation = xlDataField
.NumberFormat = strNumberFormat
End With



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot Table Calculated Datafield Caption Problem
 

Hi Debra,
It worked, THANKS! I decided to append something to the end of the Datafield
name string instead because that's hidden to the user. Thanks again.
--
Best Regards,
Dean


"Debra Dalgleish" wrote:

You can add a space to the end of the caption:

With ptDashPORTPivot.PivotFields(strDatafieldCaption)
.Orientation = xlDataField
.NumberFormat = strNumberFormat
.Caption = strDatafieldCaption & " "
End With


SaeOngJeeMa wrote:
Hello, I'm having a problem with Excel forcing "Sum of " in front of the
Caption I'm assigning to my Datafields in a Pivot Table. Please see code
below. In this case Excel takes my Caption "Backlog Coverage, Rev (K$)" and
turns it into "Sum of Backlog Coverage, Rev (K$)" on the report. On the
report I don't want the users to see that "Sum of ." Thanks in advance for
any help you can provide.
Best Regards,
Dean

CODE EXCERPT

Set ptDashPORTPivot = Worksheets("DashPORT").PivotTables("DashPORTPivot" )

strDatafieldCaption = "Backlog Coverage, Rev (K$)"
strDatafieldFormula = "(BillGrossR + BLCSDR)/1000"
strNumberFormat = "#,###;-#,###;"

ptDashPORTPivot.CalculatedFields.Add strDatafieldCaption,
strDatafieldFormula, True

With ptDashPORTPivot.PivotFields(strDatafieldCaption)
.Orientation = xlDataField
.NumberFormat = strNumberFormat
End With



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html