VBA Type Mismatch Error When Creating Pivot Cache

CarolynS

Board Regular
Joined
Oct 27, 2015
Messages
56
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

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:

Code:
'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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
It's better to use an address string in R1C1 format, especially with large data sets:

Rich (BB code):
'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
 

Forum statistics

Threads
1,081,545
Messages
5,359,438
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top