Record Pivot table Macro
 

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

Record Pivot table Macro
 

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

Record Pivot table Macro
 

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

Record Pivot table Macro
 

Hi Bob
I understand why you want it as a macro.
Try amending your code to the following, it works for me

Sub phones()

Dim myCalls As Range
Dim lastrow As Long
lastrow = ActiveWorkbook.Sheets("Sheet1").Cells(Rows.Count,
1).End(xlUp).Row
Set myCalls = ActiveWorkbook.Sheets("Sheet1").Range("A4:N" & lastrow)
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=myCalls).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
End Sub


--
Regards

Roger Govier


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