Pivottable code problem

[Ruben]

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:=xlDatabase, 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

 

[Ruben]

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]

Thanks for your help, see my other post for the cause of my problem.

Ruben

 

[Dave Peterson]

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 there:

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:=xlDatabase, _
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]

Dave,

Thanks for your great solution, it works well!

Kind regards,
Ruben