Help with a macro creating a formula for a pivot table

[Guest]

Dear experts,
I have a problem with a code to insert a formula in a Pivot Table.
The name of the items in the formula is not fixed, it depends on which
workbook I choose to run the macro, so I entered a public variable to
indicate this.
Here is the code:

ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(" Volume (MT)" & " " & Data_TimeFrame), _
"Sum of Volume (MT)" & " " & Data_TimeFrame, xlSum

Range("B7").Select
ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add "Weighted
Average PM Deviation (by Volume)", _
"='Deviation * Volume' /'"Volume (MT)" & " " & Data_TimeFrame'", True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Weighted Average PM
Deviation (by Volume)").Orientation = _
xlDataField

The second part of the code does not work!!!!! I get a syntax error.
The code " Volume (MT)" & " " & Data_TimeFrame (where Data_TiemFrame is my
public variable works well to define the Pivot Field, but not the formula!

Coudl you please help me?
Many thanks in advance,
best regards,

 

[Dave Peterson]

This is a guess. I think your string (constants & variable) doesn't quite match
what you really have in the data.

Maybe you could record a macro just to see how it shows up.

Then put this line in right before your offending line:

debug.print "****" & " Volume (MT)" & " " & Data_TimeFrame & "****"

(**** just to help see the beginning and end of the string)

Then compare that with what you got when you recorded the macro.