I shall try to work something out with de Define Name function

The reason that I want to make a macro is, because the Pivottable has to be
made from different files, the file with the telephone details is not always
the same, it's another file per month.
I want to store the macro in the Personal.xls file, not in the file with the
telephonedetail itself, so that when the user receives a new file with
telephone details, he just has to run the macro on that file to make the
Pivottable, no matter how many rows there are in the current file.

Maybe it is possible to use the =OFFSET function in the macro in de Source
data field instead of "CALLS!R4C1:R8412C14"?
I will try that.

Bob

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