[ExcelVBA] Pivot table error 13 with more than 65,535 rows

 

Dear All 

As this group has been very quiet lately I thought I would post this as a solution to a problem that nearly drove me mad in Excel 2010.

I had been using a macro to make a pivot table for quite some time when suddenly it stopped working with Error 13, Type Mismatch in this line :-

    Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=rSource)

where rSource was set up like this :-

    Set rSource = Range("a1").CurrentRegion

It happened (of course) when the row count in the source data crept beyond a 65,535 boundary. It worked perfectly with fewer rows in an older data set.

The problem seemed to be in 'SourceData:=rSource'  because I could still create the pivot table manually. I tried many variations of coding the source range and then found that even if I hard-coded the fully qualified source address it still did Error 13, which by the way is not the error you get if the pivot runs out of memory.

After many hours of Googling, I came across a mention of Named Ranges (which you can get at from menu item Formulas, Name Manager).

So I created a Named Range thus :-

    Set rSource = Range("a1").CurrentRegion

    ThisWorkbook.Names.Add Name:="SOURCE", RefersTo:=rSource

and then did :-

    Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:="SOURCE")

It worked perfectly. So 'SourceData:=' works better with a Named Range. Why ?

Here is a cut-down example of the code (removed error checking and formatting). :-

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''           '

'   Generic Pivot Table Maker - Derek Turner September 2013

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''           '

Private Sub MakePivotTable(sPivotName As String, sColumnField As String, sRowField As String, sValuesField As String)

Dim rSource As Range, rDestination As Range

Dim pivotTableCache As PivotCache, pivotTableReport As PivotTable

Const SOURCE As String = "SOURCE"

    Set rSource = Range("a1").CurrentRegion

    ThisWorkbook.Names.Add Name:=SOURCE, RefersTo:=rSource ' THIS gets over the 65,000 row limit

    Application.DisplayAlerts = False

    On Error Resume Next: Sheets(sPivotName).Delete: On Error GoTo 0

    Application.DisplayAlerts = True

    Sheets.Add

    ActiveSheet.Name = sPivotName

    Set pivotTableCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=SOURCE)

    Set rDestination = Range("a3") '

    Set pivotTableReport = pivotTableCache.CreatePivotTable( _

                TableDestination:=rDestination, TableName:=sPivotName)

    With ActiveSheet.PivotTables(sPivotName)

         .AddDataField ActiveSheet.PivotTables(sPivotName).PivotFields(sValuesField), , xlSum

         .PivotFields(sColumnField).Orientation = xlColumnField

         .PivotFields(sRowField).Orientation = xlRowField

    End With

    If sRowField = "Date" Then ' GROUP by Year and Month

        Cells(rDestination.Row + 2, 1).Group Periods:=Array(False, False, False, False, True, False, True)

    End If

    Application.ScreenUpdating = True

End Sub

'Called like this, column names MUST match your data :-

Sub MakePivotByRep() 'these must be column names in source sheet

    MakePivotTable sPivotName:="Sales By Rep", _

                         sColumnField:="Department", _

                          sRowField:="Sales Rep", _

                           sValuesField:="Net"

End Sub

Sub MakePivotByMonth() 'these must be column names in source sheet

    MakePivotTable "Sales By Month", "Department", "Date", "Net"

End Sub

If you have found this useful or have questions please comment.

Regards

Derek Turner

England

+++

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (1)

Recent Activity:

• New Members 3

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___