Thread: Type Mismatch on Pivot Table creation

The macro snippet below is a tried and true one I've been using for months. This morning it started giving me an error 13 Type Mismatch on the line beginning "ActiveWorkbook.PivotCaches.Add...". I thought maybe it was having trouble with the used range, so tried adding "ActiveSheet.UsedRange
", but that didn't help. I'd appreciate any insight anyone can provide.

Sub View3nhlf()
    Dim LastRow As Long
    Dim pt As PivotTable, pi As PivotItem
    'This macro creates the Pivot and the View3nhlf sheet from the Detail sheet
    Dim ws As Worksheet
    Set ws = Sheets("Detail")

    With Application    'code below speeds up operation
        .ScreenUpdating = False
        .EnableEvents = False

        ws.Activate
        ActiveSheet.UsedRange
        'Create the pivot
        Cells.Select
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                                       ActiveSheet.UsedRange).CreatePivotTable TableDestination:="", TableName:= _
                                       "PivotTable1", DefaultVersion:=xlPivotTableVersion10
        ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

        'Define a name for our pivot table
        Set pt = ActiveSheet.PivotTables("PivotTable1")

        'Stop the pivot from updating itself
        pt.ManualUpdate = True

I found a cause, but I'm not sure why it's a cause.

In the data source for my pivot, I have a column called "Desc", which orignally contains a 30 character description. This morning I augmented that piece by running a routine that replaces the 30 character description with a paragraph fully describing the item. After that is when I started having the problem.

What's really weird is that WITH the paragraphs in, I can manually create the PT with no problem, but my automation won't do it. Going back to the 30 character description, the automation creates the pivot with no problem.

Any clues as to the "Why" of this would be greatly appreciated.

Okay, I found this one out on my own. The problem was Excel 2003's limits, in this case an apparently undocumented one of having over 911 characters in a cell. I truncated the data, and now I'm rocking and rolling.

Thanks,
John