Stumped on vba type mismatch error (Pivot Macro)

[ablattel]

Hey All,

So I've been able to identify some factors for an error that is getting thrown in a macro I've written, but I'm really stumped on what is causing it and how to deal with it.

The basics of this macro is to take a range of cells and create a new pivot table. The macro should be able to take any number of rows and columns (typically around 8 columns w/ 100k rows). The code I'm using (and primarily testing with) looks like this

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        newData, Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:=Sheets("Pivot").Cells(8, 1), TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12

(because this code throws an error, I've also tried)

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Selection). _
        CreatePivotTable TableDestination:=Sheets("Pivot").Range("A3"), TableName:="PivotTable1"

and 9 times out of 10 this code works fine. However sometimes when running the macro with new data I get an error

"Run-Time error '13':

Type mismatch"

and the debugger points to the code I posted above.

So now I really have no idea what is causing this, so any ideas and help is greatly appreciated. By trying to reproduce the error, my best guess is that the problem is linked to the size of the data, but this is inconsistent (and doesn't make much sense to me).

Results from trying to reproduce error using:
1) Data set 1 - My standard data that I use. Size 12 columns, 110k rows. Has some blank cells. --> This data has worked every time I've run it, with 1 exception as follows. I tried to double the data set (so essential copy-paste the data to the bottom of the set) to make it 220k rows. This then broke the code, pretty much as expected. HOWEVER, deleting the duplicate rows and trying to run the exact same data as I normally do ALSO broke the code. FURTHER, closing without saving and then loading the file again, and using the original data still breaks the code. **Loading an earlier version with identical data and code still works fine.**

2) Data set 2 - A new data set. Size 7 columns, 96k rows. Has more blank cells. Breaks the code the 1st time I ran it. Shrinking it down to ~50k and the code works.

Again, any and all help/suggestions appreciated. Let me know if there is other information I should post to help out.

Thanks,
Arran

 

[Jerry Sullivan]

Hi Arran,

Here are a few things to consider...

1. Is the PivotTable (PT) name already being used in that worksheet? This is a common problem when the PT name is hard-coded into the macro. One way to avoid this is to let Excel assign the name of the PT.

2. In your first example you use the variable newData. Where is that assigned and is it possible it isn't set to a range?

Your second example uses Selection, which could also not be giving you the range you intended.

3. If these two references are correctly assigned to a range, another potential problem is that the range might not be a valid PivotTable datasource. This can happen if you have blanks in the header, or only two rows of data.

 

[ablattel]

Hey Jerry, thanks for the feedback. Here are the answers to your questions.

1. Is the PivotTable (PT) name already being used in that worksheet? This is a common problem when the PT name is hard-coded into the macro. One way to avoid this is to let Excel assign the name of the PT.

-->The macro puts the Pivot Table on a brand new worksheet (in part to ensure no name conflict could exist). That said, let me know what the code is to let Excel assign the name and I could give that a try.

2. In your first example you use the variable newData. Where is that assigned and is it possible it isn't set to a range?

Your second example uses Selection, which could also not be giving you the range you intended.

-->I've tested this by using the "Immediate Pane." Typing in "?newData.address" (or selection) always gives the range I'm looking for even when an error is thrown.

3. If these two references are correctly assigned to a range, another potential problem is that the range might not be a valid PivotTable datasource. This can happen if you have blanks in the header, or only two rows of data.

-->I think this may be the answer, but it is not either of the two things you have listed. They always have at least 200 rows (usually ~100k) and the first row is always populated with a header (8-12 columns, easy to visually check). Further, I can always use the exact same data and set up a Pivot Table with it, I just have to do it manually.

Again, thanks for taking a look at this and let me know if you have any further thoughts.

Cheers,
Arran

 

[Jerry Sullivan]

Arran,

I believe your hunch was correct on size of the datasource range.

The PivotCaches.Create Method appears to fail ("Run-Time error '13':Type mismatch")
when there are more than 65,536 rows.
You can test that boundary with the code below using a one-column data source.

Sub FindTypeMismatchError()
    Dim newData As Range
    Set newData = Sheets("Sheet1").Range("A1:A65536") 'works
 '   Set newData = Sheets("Sheet1").Range("A1:A65537") 'fails
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        newData).CreatePivotTable TableDestination:=Sheets("Pivot").Range("A3"), _
        TableName:="PivotTable1"
End Sub

It's no coincidence that this is the same number of rows that are in an xl2003 worksheet.
Application.Min and some other VBA functions exhibit this same behavior.

As a work around, you might consider using a Named Range (either a fixed or Dynamic Named Range).
The code below worked using MyData, a Dynamic Named Range representing 200,000 Rows.

Sub Alternative()
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!MyData"). _
        CreatePivotTable TableDestination:=Sheets("Pivot").Range("A3"), _
            TableName:="PivotTable1"
End Sub