Whenever I want to create an Excel macro, I use the record function because
I am not aan VBA specialist.

I have a detailed list of telephone calls in a sheet CALLS and I want to
make a Pivot table with the phonenumber and the total of the call duration
per phonenumber.
When I start recording the macro, the actual range of the data (in this case
from row 4 to row 8412) is inserted in the field 'SourceData' of the macro.
How can I adapt the SourceData in this macro, so it also works whenever
there is another range in the sheet (another number of rows).


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"CALLS!R4C1:R8412C14").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Phone nr"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Duration")
.Orientation = xlDataField
.Caption = "Sum of Duration"
.Function = xlSum
.NumberFormat = "[h]:mm:ss"
End With



Kind regards,

Bob