Thank you Derek for that code. I have experienced issues in code when the count was higher than integer bounds, but usually I get “overflow”. Any insight on why it did not flag as out of range?
Tim
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of Derek Turner
Sent: Friday, September 13, 2013 6:10 AM
To: ExcelVBA@yahoogroups.com
Subject: [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 (2) |
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
----------------------------------
Tidak ada komentar:
Posting Komentar