I have tried:

Dim strTestVariable As String
Dim strCalculatedField As String

strTestVariable = "Volume (MT) Oct_2004"
strCalculatedField = "Weighted Average PM Deviation (by Volume)"

'Delete variable if it already exists
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").CalculatedF ields(test).Delete
On Error GoTo 0
ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add
strCalculatedField, _
"=Deviation * Volume/" & strTestVariable, True


ActiveSheet.PivotTables("PivotTable1").PivotFields (strCalculatedField).Orientation = xlDataField
Range("B4").Select
With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
.Orientation = xlColumnField
.Position = 1
End With

This should work. I believe the problem arises from using ', which should be
deleted:
Instead of
"='Deviation * Volume' /' Volume (MT) Oct_2004'"
try
"=Deviation * Volume / Volume (MT) Oct_2004"

Let me know if this works!

Martin

"Valeria" wrote:

Dear experts,
I am having again a problem with writing a code for a calculated field of a
pivot table.
The calculation of this field is performed with a variable, a string that
should represent the ' Volume (MT) Oct_2004', which is in the code below.
This is because I will change the database every month and there will be a
November, December, etc instead of October.

ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add
"Weighted Average PM Deviation (by Volume)", _
"='Deviation * Volume' /' Volume (MT) Oct_2004'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Weighted Average PM
Deviation (by Volume)").Orientation = _
xlDataField

If I now change the code with a test variable
test = " Volume (MT) Oct_2004"
this does not work at all, Excel does not recognize it. (error meassage "an
item name cannot be found", but I am sure the string is correct)

Could you please let me know how to do it? I am getting desperate at it!!

Many thanks in advance.
Best regards,


--
Valeria