VBA Error (Run time error 13, Type Mismatch)

[rdw72777]

I'm getting a macro error when i run the following macro. I'm getting a "Run Time '13': Type mismatch" error on the last line of code (Set PivoTable code....).


I've run this code frequently for the last few months and have made no changes to the code. Anyone know in detail what the type mismatch error indicates.

I've added more rows to the data that the pivot table sources from, but since I can create the pivot manually I think that's not the issue.

Thanks,

Ryan

Private Sub RollupData()
'
'   Go through each row in ET worksheet
'   If there are multiple ET codes for the same project, FACTDesc, and month
'   Summarize the data into one row
'
'   This will be done by creating a new ws as a pivot table
'

'
    Dim PivotTbl As PivotTable
    Dim destws As Worksheet
    Dim DestRng As Range
    
    ActiveWorkbook.Sheets("ETPivotTbl").Columns("A:T").Delete Shift:=xlToLeft
    Set DestRng = ActiveWorkbook.Sheets("ETPivotTbl").Cells(1, 1)
    
    '
    '   Delete existing pivot table
    '
    
    
    
    Set PivotTbl = ActiveWorkbook.PivotCaches.Create(xlDatabase, ETws.Cells(1, 1).CurrentRegion, _
        xlPivotTableVersion12).CreatePivotTable(DestRng, "aaa", True, xlPivotTableVersion12)

 

[Norie]

Ryan

I think I've seen that before and I think I 'fixed' it by first creating the pivot cache and then creating the pivot table from that.

Something like this perhaps - I'll see if I can find the code.

Couldn't find it but this very rough code works.

Option Explicit
 
Sub test()
Dim ETws As Worksheet
Dim ws As Worksheet
Dim rngSrc As Range
Dim ptCache As PivotCache
Dim ptTable As PivotTable
Dim ptFld As PivotField
 
    Set ETws = Sheet1
 
    Set rngSrc = ETws.Range("A1").CurrentRegion
 
    Set ws = Worksheets.Add
 
    Set ptCache = ThisWorkbook.PivotCaches.Add(xlDatabase, rngSrc)
 
 
 
    Set ptTable = ws.PivotTables.Add(PivotCache:=ptCache, TableDestination:=ws.Range("A1"), TableName:="NewPivot" & Format(Time, "hhmmss"))
 
    Set ptFld = ptTable.PivotFields(rngSrc.Cells(1, 1).Value)
 
    ptFld.Orientation = xlRowField
    ptFld.Position = 1
 
    Set ptFld = ptTable.PivotFields(rngSrc.Cells(1, 3).Value)
 
    ptFld.Orientation = xlColumnField
    ptFld.Position = 1
 
    Set ptFld = ptTable.AddDataField(ptTable.PivotFields(rngSrc.Cells(1, 2).Value), "Value", xlSum)
 
    ptFld.Orientation = xlDataField
    ptFld.Position = 1
 
End Sub

I wouldn't post the result because the tiny data set I based it on is an embarassment.

 

[rdw72777]

Turned out the whole issue was with the new data I added in. Some of the new data I added in had more than 256 characters, and I guess the pivot tables don't want to handle such items. By trimming the underlying data, I find that my macro is fine and it was the source data that was the problem.