Hi, I'm using VBA to create a pivot table from a sheet with 72k rows of
data. When it was at 50-60k, the pivot worked fine but when I use more
than that I get a mismatch error at this line below
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)
and I dont understand why. I've tried to fix it but unsure where to go from
here. anyone know how can I fix this?
Here is my code in its entirety for this part:
Function CreatePivotTable()
Application.PivotTableSelection = True
Dim Combined As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set Combined = Worksheets("combined")
Set pivotSheet = Worksheets("Summary")
' Delete any prior pivot tables
For Each PT In pivotSheet.PivotTables
PT.TableRange2.Clear
Next PT
' Define input area and set up a Pivot Cache
FinalRow = Combined.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = Combined.Cells(1,
Application.Columns.Count).End(xlToLeft).Column
Set PRange = Combined.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)
'the CreatePivotTable method to create a blank pivot table based on the
defined pivot cache
Set PT =
PTCache.CreatePivotTable(TableDestination:=pivotSheet.Range("A2"), _
TableName:="PivotTable1")
'turn off updates
PT.ManualUpdate = True
'.AddFields method, you can specify one or more fields that should be
in the row, column, or page area of the pivot table
' Set up the row & column fields
PT.AddFields RowFields:=Array("April Final Organization", "April Final
Region", "April Final MGR", "April Final Login", "April Final Job Type"), _
ColumnFields:=Array("Quota Qtr", "Quota Month")
' Set up the data fields
With PT.PivotFields("Sales Credit")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
.Position = 1
End With
With PT.PivotFields("April Final Login")
.Subtotals(1) = False
End With
pivotSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel,
True
pivotSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight16"
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
'notify user of completion
MsgBox "Pivot Table is all set"
'show new pivot table
Worksheets("Summary").Select
End Function
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
