Macro for pivot table 1

[DanIT]

I have to create a macro to make a pivot table from a sheet of data.

1. Each time the data will have the same column headings.
2. There will be different numbers of rows each time.

I cannot get Excel to handle the second part. If I put in a high number (10000) it gives incorrect data as output. I can get VBA to count the rows, but don't know how to insert the number in to this statement from the macro:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R140C22&quot.CreatePivotTable TableDestination:="" _
, TableName:="PivotTable1"

In the above example, the worksheet is called Data and there are 140 rows of data, including labels.

How can I replace the 140 with a variable such as "x"?

Thanks,
Dan

 

[ETID]

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R"+trim(str$(x))+"C22&quot.CreatePivotTable TableDestination:="" _
, TableName:="PivotTable1"

 

[DanIT]

Thank you! It worked excellently.

Dan