Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable code problem
Hello,
I have a problem with making a pivottable from my data with visual basic. When running the code visual basic says that there is an invalid procedure call or an invalid argument. The part of the code that is wrong according to visual basic is the uppermost part(from beginning till 'xlpivottableversion10'): Sub CreatingPivottable ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Bewerkte data'!R1C1:R10000C18").CreatePivotTable TableDestination:= _ "Blad1!R3C1", TableName:="Draaitabel8", DefaultVersion:= _ xlPivotTableVersion10 '....(other part of code)... End Sub 'Bewerkte data' is the name of the sheet with the data that is used for making the pivottable and 'blad1' is the name of the sheet where the pivottable has to be placed (this sheet exists before I start the code). 'Draaitabel8' is the name of the pivottable. I start the code with a button on the sheet 'Bewerkte data'. I hope the problem is clear, if it is not let me know. It would be great if someone could help me! Ruben |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable code problem
After some research I found the cause of my problem: the error occurs because
the destination worksheet and the range for the new PivotTable are specified in the recorded macro. Therefore, the recorded macro is not dynamic. Additionally, the recorded macro cannot be played repeatedly. This means that I have to replace TableDestination:= "Blad1!R3C1" by Table Destination="". This means that the pivottable is placed on a new sheet, and not on "Blad1", where I would like to place it. If someone knows how I could place the table on Blad1 without an error let me know! Ruben "Ruben" wrote: Hello, I have a problem with making a pivottable from my data with visual basic. When running the code visual basic says that there is an invalid procedure call or an invalid argument. The part of the code that is wrong according to visual basic is the uppermost part(from beginning till 'xlpivottableversion10'): Sub CreatingPivottable ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Bewerkte data'!R1C1:R10000C18").CreatePivotTable TableDestination:= _ "Blad1!R3C1", TableName:="Draaitabel8", DefaultVersion:= _ xlPivotTableVersion10 '....(other part of code)... End Sub 'Bewerkte data' is the name of the sheet with the data that is used for making the pivottable and 'blad1' is the name of the sheet where the pivottable has to be placed (this sheet exists before I start the code). 'Draaitabel8' is the name of the pivottable. I start the code with a button on the sheet 'Bewerkte data'. I hope the problem is clear, if it is not let me know. It would be great if someone could help me! Ruben |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable code problem
Thanks for your help, see my other post for the cause of my problem.
Ruben "Roger Govier" wrote: Hi Ruben The code runs perfectly fine for me in both XL2003 and XL2007 I changed the names of the sheets to Sheet1 and Sheet2 respectively for testing on my system. -- Regards Roger Govier "Ruben" wrote in message ... Hello, I have a problem with making a pivottable from my data with visual basic. When running the code visual basic says that there is an invalid procedure call or an invalid argument. The part of the code that is wrong according to visual basic is the uppermost part(from beginning till 'xlpivottableversion10'): Sub CreatingPivottable ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Bewerkte data'!R1C1:R10000C18").CreatePivotTable TableDestination:= _ "Blad1!R3C1", TableName:="Draaitabel8", DefaultVersion:= _ xlPivotTableVersion10 '....(other part of code)... End Sub 'Bewerkte data' is the name of the sheet with the data that is used for making the pivottable and 'blad1' is the name of the sheet where the pivottable has to be placed (this sheet exists before I start the code). 'Draaitabel8' is the name of the pivottable. I start the code with a button on the sheet 'Bewerkte data'. I hope the problem is clear, if it is not let me know. It would be great if someone could help me! Ruben |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable code problem
If I could use column A to determine the last used row, then I could drop down a
couple of rows and add the pivottable the Option Explicit Sub CreatingPivottable() Dim DestCell As Range With Worksheets("blad1") Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(3, 0) End With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _ SourceData:="'Bewerkte data'!R1C1:R10000C18").CreatePivotTable _ TableDestination:=DestCell, TableName:="", _ DefaultVersion:=xlPivotTableVersion10 '....(other part of code)... End Sub Personally, I'd want those pivottables on different worksheets. One of the huge benefits of the pivottable is to be able to "pivot" them and see different summaries. I wouldn't want any of my pivottables to be constrained (in size) by any of the other pivottables on that sheet. Ruben wrote: Thanks for your help, see my other post for the cause of my problem. Ruben "Roger Govier" wrote: Hi Ruben The code runs perfectly fine for me in both XL2003 and XL2007 I changed the names of the sheets to Sheet1 and Sheet2 respectively for testing on my system. -- Regards Roger Govier "Ruben" wrote in message ... Hello, I have a problem with making a pivottable from my data with visual basic. When running the code visual basic says that there is an invalid procedure call or an invalid argument. The part of the code that is wrong according to visual basic is the uppermost part(from beginning till 'xlpivottableversion10'): Sub CreatingPivottable ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Bewerkte data'!R1C1:R10000C18").CreatePivotTable TableDestination:= _ "Blad1!R3C1", TableName:="Draaitabel8", DefaultVersion:= _ xlPivotTableVersion10 '....(other part of code)... End Sub 'Bewerkte data' is the name of the sheet with the data that is used for making the pivottable and 'blad1' is the name of the sheet where the pivottable has to be placed (this sheet exists before I start the code). 'Draaitabel8' is the name of the pivottable. I start the code with a button on the sheet 'Bewerkte data'. I hope the problem is clear, if it is not let me know. It would be great if someone could help me! Ruben -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivottable code problem
Dave,
Thanks for your great solution, it works well! Kind regards, Ruben "Dave Peterson" wrote: If I could use column A to determine the last used row, then I could drop down a couple of rows and add the pivottable the Option Explicit Sub CreatingPivottable() Dim DestCell As Range With Worksheets("blad1") Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(3, 0) End With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _ SourceData:="'Bewerkte data'!R1C1:R10000C18").CreatePivotTable _ TableDestination:=DestCell, TableName:="", _ DefaultVersion:=xlPivotTableVersion10 '....(other part of code)... End Sub Personally, I'd want those pivottables on different worksheets. One of the huge benefits of the pivottable is to be able to "pivot" them and see different summaries. I wouldn't want any of my pivottables to be constrained (in size) by any of the other pivottables on that sheet. Ruben wrote: Thanks for your help, see my other post for the cause of my problem. Ruben "Roger Govier" wrote: Hi Ruben The code runs perfectly fine for me in both XL2003 and XL2007 I changed the names of the sheets to Sheet1 and Sheet2 respectively for testing on my system. -- Regards Roger Govier "Ruben" wrote in message ... Hello, I have a problem with making a pivottable from my data with visual basic. When running the code visual basic says that there is an invalid procedure call or an invalid argument. The part of the code that is wrong according to visual basic is the uppermost part(from beginning till 'xlpivottableversion10'): Sub CreatingPivottable ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Bewerkte data'!R1C1:R10000C18").CreatePivotTable TableDestination:= _ "Blad1!R3C1", TableName:="Draaitabel8", DefaultVersion:= _ xlPivotTableVersion10 '....(other part of code)... End Sub 'Bewerkte data' is the name of the sheet with the data that is used for making the pivottable and 'blad1' is the name of the sheet where the pivottable has to be placed (this sheet exists before I start the code). 'Draaitabel8' is the name of the pivottable. I start the code with a button on the sheet 'Bewerkte data'. I hope the problem is clear, if it is not let me know. It would be great if someone could help me! Ruben -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with creating pivottable | Excel Discussion (Misc queries) | |||
problem with pivotTable in Excel | Excel Discussion (Misc queries) | |||
PivotTable grouping by Time Elapsed problem | Excel Discussion (Misc queries) | |||
PivotTable grouping problem | Excel Discussion (Misc queries) | |||
Pivottable Problem | Charts and Charting in Excel |