I do not know exactly what happends but do try first those syntax:

myworkbook.Names.Add Name:=NameRef, RefersTo:=PivotSourceData

or

myworkbook.Names.Add (NameRef, PivotSourceData)


and also check taht the sourceworksheet is set ok