Hi Bob

I wondered why you were using a macro to create the PT each time.
Unless there is some specific reason for this, I would just make the
source in the PT itself a dynamic range.
On the Calls sheet, InsertNameDefinemyCalls Refers to
=OFFSET($A$4,0,0,COUNTA($A:$A),14)

With the PT, from the PT toolbar, choose the Pivot Table
dropdownWizardBackSource data =myCalls
There will then be no need to re-create the PT, just click on Refresh
and it will include all data from the Calls sheet.

--
Regards

Roger Govier


"BobG" wrote in message
...
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