Record Pivot table Macro

  • Thread starter Thread starter BobG
  • Start date Start date
B

BobG

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:=xlDatabase, 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
 
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, Insert>Name>Define>myCalls Refers to
=OFFSET($A$4,0,0,COUNTA($A:$A),14)

With the PT, from the PT toolbar, choose the Pivot Table
dropdown>Wizard>Back>Source 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.
 
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 said:
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, Insert>Name>Define>myCalls Refers to
=OFFSET($A$4,0,0,COUNTA($A:$A),14)

With the PT, from the PT toolbar, choose the Pivot Table
dropdown>Wizard>Back>Source 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 said:
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:=xlDatabase, 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
Click to expand...
Click to expand...
 
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:=xlDatabase, _
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 said:
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 said:
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, Insert>Name>Define>myCalls Refers to
=OFFSET($A$4,0,0,COUNTA($A:$A),14)

With the PT, from the PT toolbar, choose the Pivot Table
dropdown>Wizard>Back>Source 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 said:
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:=xlDatabase, 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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Avoiding an extra sheet when creating a pivot table 3
Playing back a Pivot Table macros 2
Pivot Table Macro with dynamic source data 1
Please Help! Need to add a Variable Range in my Excel Macro withPivot Tables 3
Creating Pivot Tables with macro Problem 4
VBA for pivot tables 2
PivotTable Macro not working 2
Pivot table Macro help!!! 3

Back
Top