I need to create a pivot table with vba. Using the macro recorder I understand the statement is:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Sheet1!R1C1:R398C6", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="Tabella_pivot1", _
DefaultVersion:=xlPivotTableVersion14

The SourceData parameter specifies where the input data are. In the above case data are on sheet1 and the data range is row 1 col 1 to row 398 to col 6.
I need to change this value according to what the user store in Sheet1. For this reason I have modified the above code in this way:


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"Sheet1!R1C1:R" & Mid(Str(UR), 2) & "C6", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Pivot!R3C1", TableName:="Tabella_pivot1", _
DefaultVersion:=xlPivotTableVersion14

where UR is variable declared as Integer containing the last row range.

Unfortunately, I get a run time error 5.

Please, can somebody tell me where I am wrong? Thanks