PDA

View Full Version : Need help with pivot table data range



kualjo
03-06-2008, 08:16 AM
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
03-06-2008, 08:37 AM
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

kualjo
03-06-2008, 09:11 AM
That did it! Thanks!