Problem with a pivot table in my macro

[punter]

Hi,

I'm working on a macro. One of the things it has to do is create a pivot table. It works but I can't promise that range will always be 9505. It may be more lines or less in the future. How do I change the code so it will pick up all the data on sheet regardless of how many lines there are? Column AF will always have data in it so maybe there is a way to tell it to take everything down through the last cell with data in that column.

Any thoughts?

Thanks

Eddie

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Corp Paying'!R1C1:R9505C32").CreatePivotTable TableDestination:="", _
TableName:="PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4")
.ColumnGrand = False
.RowGrand = False
End With
ActiveSheet.PivotTables("PivotTable4").AddFields RowFields:="Shipper_Company" _
, ColumnFields:="GL Code"
ActiveSheet.PivotTables("PivotTable4").PivotFields("AmtDue").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False

[VBA Noob]

Hi,

This site might help with your range problem

http://www.contextures.com/xlPivot01.html

VBA Noob