VBA runtime error 5 when creating Pivot Table-CodePudding

I searched the internet code and was able to put together a macro to create a pivot table through VBA. I keep getting a Run time error '5': Invalid procedure call or argument when it gets to the creating the Pivot Table cache.

Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="TBPvt")

I have this same code create another pivot table and it works fine. I put both sets of code side by side and the only updates between the code is myDestinationWorksheet and the TableName in the code above. So I'm not sure what I did wrong.

Sub CreateTBpvt()
'declare variables to hold row and column numbers that define source data cell range
    Dim myLastRow, myLastColumn As Long
    
    Dim StartHere As String
    StartHere = Sheets("Start Here").Range("C3").Value
 
    'declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String
 
    'declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable
 
    'identify source and destination worksheets
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("TrialBalance")
        Set myDestinationWorksheet = .Worksheets("TB Pivot")
    End With
 
    'obtain address of destination cell range
    myDestinationRange = myDestinationWorksheet.Range("A1").Address(ReferenceStyle:=xlR1C1)
 
    With mySourceWorksheet.Cells
 
        'find last row and last column of source data cell range
        myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
 
        'obtain address of source data cell range
        mySourceData = .Range(.Cells(2, "A"), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
 
    End With
 
    'create Pivot Table cache and create Pivot Table report based on that cache
    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="TBPvt")
 
    'add, organize and format Pivot Table fields
    With myPivotTable
        .PivotFields("Account").Orientation = xlPageField
        .PivotFields("Descr").Orientation = xlRowField

        With .PivotFields(StartHere)
            .Orientation = xlDataField
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
    End With
End Sub

CodePudding user response：

The destination sheet has a space in its name, so you need to surround it with single quotes:

TableDestination:="'" & myDestinationWorksheet.Name & "'!" & myDestinationRange