Need help with pivot table data range

[kualjo]

I recorded a macro to build a pivot table, and in the part of the code where it selects the data range, it is a specific number of rows. My reports will vary in the number of rows of data everytime they are run, so this is not going to work. The relevant code currently looks like this:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "Sheet1!R1C1:R14457C7").CreatePivotTable TableDestination:="", TableName:= "PivotTable1", DefaultVersion:=xlPivotTableVersion10

How can I change this to capture the entire range of data, however many rows it may be?

Thank you.

[Bob Phillips]

[vba]

Dim LastRow As Long

With ActiveWorkbook

With .Worksheets("Sheet1")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R" & LastRow & "C7").CreatePivotTable _
TableDestination:="", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
End With
[/vba]

[kualjo]

That did it! Thanks!