Macro To Create Pivot Table with Dynamic Source Data

Hi All,

I am attempting to use a macro to create a pivot table in excel. However, the source data will contain a different amount of rows each time I run the macro. By simply recording the macro I get this code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R5249C95").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

I can see that the problem is that the code builds the pivot table from source data rows 1 through 5249. Does anyone know how to get the macro to set the source data to however many rows I have at the time I run it?

Thanks in advance!