Pivot table range

[Apple08]

Hi everyone

I have syntax error on the code below. I can't find out the problem, please could anyone help?

Sheets("Combined Report - Modules").Select
Dim Last_Row As Long
Last_Row = Range("A" & Rows.Count).End(xlUp).Row

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Combined_Data!R1C1:R" & Last_Row & "C13", Version:=xlPivotTableVersion14).CreatePivotTable_
        TableDestination:="Combined Report - Modules"!R3C1", TableName:="PivotTable1", DefaultVersion_
        :=xlPivotTableVersion14

Also, is there a difference of the PivotTableVersion14 or 15? Many thanks.

 

[SpillerBD]

For the version, see https://msdn.microsoft.com/en-us/li...ce.interop.excel.xlpivottableversionlist.aspx
I use Excel 2013 so my macros use xxlPivotTableVersion15

I found that defining a range variable outside the statement can work best and helps limit or pinpoint certain errors.
If "PivotTable1" already exist, I think it will error out. Its been so long since I wrote mine. If I remember correctly, I let mine error out because it stops the macro preventing the existing PivotTable from being destroyed.
Also, I use a more meaningful name for my PivotTables, especially when I've taken the time to write a macro.

 

[Apple08]

Thanks. I have updated the code but it still doesn't work. The syntax error highlighted the := after Tabledestination:

 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Combined_Data!R1C1:R" & Last_Row & "C13", Version:=xlPivotTableVersion15).CreatePivotTable_
        TableDestination:=Worksheets("Combined Report - Modules"!R3C1", TableName:="ByModule", DefaultVersion _
        :=xlPivotTableVersion15

 

[SpillerBD]

Something seems off about the flow of that text and the exclamation point....
See how the macro recorder ends recording that reference and use in your code.