Error in Generating Pivot Table from Macro

[samantha87]

Hi I keep getting errors (either Range error or invalid procedure call error) when trying to run a pivot table using macro.
I've attempted to insert a single quote to my table destination but it raises another error i.e. invalid procedure call. I don't know where went wrong??
I'm also thinking if i should give the pivot table a name, which im completely lost on how to. I have multiple pivot tables to create for this sheet also, not just this. Is there a way that I can easily define each of them and create it?
Here's my code:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Dim Lastrow As Integer
Dim Table1 As Object
Dim DataSource As Object

Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Set DataSource = ThisWorkbook.ActiveSheet.Range("A1:CF" & Lastrow)
Set Table1 = ThisWorkbook.Sheets("Table1-Data")

DataSource.Select


<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Selection, Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="'Table1'!R5C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion15</code></code></pre>

 

[Norie]

What happens if you replace SourceData:=Selection with SourceData:=DataSource and replace TableDestination:="'Table1'!R5C1" with TableDestination:="''Table1-Data''!R5C1"?

By the way, VBA/Excel will name the pivot table automatically.

PS Is the data actually on 'Table-Data' or another worksheet?

 

[samantha87]

Hi Norie,

Thanks for your help. I tried replacing "Selection" with "DataSource" and putting in "-Data" in addition to "Table1". It still doesn't work.

Yes, but noticed that the pivot table cached will rename the pivot table added everytime the macro is being ran.

Yes, the data is actually referring from another worksheet.