Macro Works in Excel 2013 but NOT in Excel 2016

[Small Paul]

Hi

I have a number of macros I have written on Excel 2013. I am now transferring them across to other users' machines as I am leaving. They all run Excel 2016. For some reason, the macros fail on 2016.

The start of one macro is as follows:

Sub Daily_Open_Trades_Pivot()
'
' Daily_Open_Trades_Pivot Macro
'
    Dim lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).row
    sheets("Sheet1").delete
    sheets.Add.Name = "Pivot"
    range("B2").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=xlPivotTableVersion15).CreatePivotTable TableDestination _
        :="Pivot!R2C2", TableName:="PivotTable10", DefaultVersion:= _
        xlPivotTableVersion15
    sheets("Pivot").Select

Macro fails AFTER selecting cell B2

Does anybody know why this might happen?

Cheers
Small Paul.

 

[dnorm]

Hi Paul

The only way I managed to get a debug was to change thetable numbers at the point you say failed.

I would go through and check the table names against themacro.

 

[Fluff]

Whilst I know nothing about pivots having never used them, you may need to change

xlPivotTableVersion15

to

xlPivotTableVersion[COLOR=#ff0000]16[/COLOR]

both times it occurs.

 

[Jon Peltier]

That shouldn't cause failure of the code, it just means the code in 16 will make a pivot table/cache with the features of Excel 15.

Make sure the named table exists, and no pivot table already has the name you want to give to the new pivot table. Also, make sure that adding a pivot table in the target range will not infringe with existing data.

By the way, what is it error? Not error number, because these are reused excessively, but error message.