Run time error code 5 for VBA to create Pivot able

  • Thanks to all in advance for attempting to help, as it has been a long time since i had to play with VBA

    There are two sections of code below, both are creating Pivot Tables into existing worksheets within the workbook.

    The first section works perfectly time after time and i can run it multiple times without getting any errors. The second section is an almost identical copy leaving aside the destination is a different worksheet using a different table name. The code falls over with a run time error 5 at the point of trying o actually create the pivot table.

    Any helps to resolve is much appreciated as i have another six pivot tables to put into the same workbook, mos of which read form the same data tab.

    This piece works fine.

    This next section is the one with the issue where i am unable to work out the issue.

    Hopefully someone will be able to assist. In both cases i have had to cut the code after the create section due to character restraints here.


  • It's the presence of the £ sign in the sheet name, which means you have to wrap the name in single quotes, as you would in a formula. (it's better practice to just do this anyway so it will work with any sheet name):

    1. Set MPT = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & MSW.Name & "'!" & MSR).CreatePivotTable(TableDestination:="'" & MDW.Name & "'!" & MDR, TableName:=MDN)

    Note that I've done it for both sheet names to be safe.

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why