Pivot Table source data reference in VBA

[heavnstrl]

I am creating a pivot table in which the data source (on a sheet named "results&quot has a variable number of rows. I can hard code the number of rows but would prefer to just refer to the 'used range' instead. The statement calling the pivot table currently looks like this.

ActiveWorkbook.PivotCaches.add(SourceType:=xlDatabase, SourceData:= _
"Results!R1C1:R3000C36&quot.CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

Whenever I substitute a variable referencing the used range I get a compiler error "Expected list separater or )". Does anyone have any suggestions?

 

[heavnstrl]

Figured out my own question. Adding code as follows fixed the problem:

prows = resultsheet.UsedRange.Rows.Count

ActiveWorkbook.PivotCaches.add(SourceType:=xlDatabase, SourceData:= _
"Results!A1:AJ" & prows).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10