Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can you record a macro that will create/format a pivot tablle? | Excel Discussion (Misc queries) | |||
record a macro to update pivot table | Excel Discussion (Misc queries) | |||
Record Macro to Create Pivot Table | Excel Discussion (Misc queries) | |||
how to add a record to a pivot table | Excel Worksheet Functions | |||
Record Pivot Table Macro | Excel Discussion (Misc queries) |