VBA Type Mismatch Error When Creating Pivot Cache

[CarolynS]

Hi
I have a worksheet containing code that I have used succesfully many times in the past to produce a fortnightly report (raw data is normally around 10,000 to 15,000 rows). I'm now trying to produce a report for all of last year and have over 279,000 rows of raw data. This initally caused issues with some of my loops and I had to change my variables from integers to long however when I get to the section of code that creates the 1st pivot table, I'm getting a "Type Mismatch" error on the following line of code

    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
    xlDatabase, SourceData:=PRange)

This has worked ok in the past so I'm not sure if it's to with the fact I changed some of the variables used earlier on in my code (the variables within the create pivot table part of my code haven't changed) or something else entirely?

Full code for creating the pivot table:

'Create Pivot table for regs by user
    
    Set WSps = Worksheets("Utilisation Regs Raw")
    Set WSpd = Worksheets("RegsbyUser")
    ' Delete any prior pivot tables
    For Each pt In WSpd.PivotTables
    pt.TableRange2.Clear  'currently no pivot tables in the worksheet
    Next pt
    ' Define input area and set up a Pivot Cache
    FinalRow = WSps.Cells(Rows.Count, 1).End(xlUp).Row
    FinalCol = WSps.Cells(1, Columns.Count). _
    End(xlToLeft).Column
    Set PRange = WSps.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
    xlDatabase, SourceData:=PRange)
    ' Create the Pivot Table from the Pivot Cache
    Set pt = PTCache.CreatePivotTable(TableDestination:=WSpd. _
    Cells(3, 1), TableName:="PivotTable1")
    ' Turn off updating while building the table
    pt.ManualUpdate = True
    ' Set up the row & column fields
    pt.AddFields RowFields:="Person - Username"
    ' Set up the data fields
    With pt.PivotFields("Completed Courses - Registration Date")
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
    End With
    ' Calc the pivot table
    pt.ManualUpdate = False
    pt.ManualUpdate = True

The above is part of a much bigger piece of code but I haven't included it all here due to the size.

I am using Excel 2007


Thanks

 

[RoryA]

It's better to use an address string in R1C1 format, especially with large data sets:

'Create Pivot table for regs by user

    Set WSps = Worksheets("Utilisation Regs Raw")
    Set WSpd = Worksheets("RegsbyUser")
    ' Delete any prior pivot tables
    For Each PT In WSpd.PivotTables
        PT.TableRange2.Clear  'currently no pivot tables in the worksheet
    Next PT
    ' Define input area and set up a Pivot Cache
    FinalRow = WSps.Cells(Rows.Count, 1).End(xlUp).Row
    FinalCol = WSps.Cells(1, Columns.Count). _
               End(xlToLeft).Column
    Set PRange = WSps.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                SourceData:="'" & WSps.Name & "'!" & PRange.Address(ReferenceStyle:=xlR1C1))
    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSpd.Cells(3, 1), TableName:="PivotTable1")
    
    ' Turn off updating while building the table
    PT.ManualUpdate = True
    ' Set up the row & column fields
    PT.AddFields RowFields:="Person - Username"
    ' Set up the data fields
    With PT.PivotFields("Completed Courses - Registration Date")
        .Orientation = xlDataField
        .Function = xlCount
        .Position = 1
    End With
    ' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True

 

[CarolynS]

Thank you, that has worked