Hi there, I'm having a little bit of trouble creating a pivot table using VBA, and not sure why, given that I'm doing it as part of a function that's working fine on a different project and being called in exactly the same way?
The macro fails on the
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Target.Range("A1:R" & LastRow))
step
with the error message: Method 'Add' of object 'PivotCaches' failed. I get a similar message if I try pivotcaches.create.
any help would be much appreciated.
here's the function:
Function PivotTable(RecipientWksht As String, TargetWksht As String, PivotStRng As Range, TblNm As String, TblLbl As String) As String
With Application
ScreenUpdate = .ScreenUpdating
.ScreenUpdating = False
.DisplayAlerts = False
End With
Dim LastRow, LastCol As Integer
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim i, j As Integer
Dim Recipient, Target As Worksheet
Dim PivotFinish As Range
Set Recipient = Worksheets(RecipientWksht)
Set Target = Worksheets(TargetWksht)
LastRow = Target.Cells(Rows.Count, "A").End(xlUp).Row
LastCol = Target.Cells(1, Columns.Count).End(xlToLeft).Row
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Target.Range("A1:R" & LastRow))
Set PT = PTCache.CreatePivotTable(tablename:=TblNm, tabledestination:=PivotStRng)
With Recipient.PT(TblNm)
.EnableDrilldown = False
.SaveData = False
.ColumnGrand = False
.RowGrand = False
With .PivotFields("Account")
.Orientation = xlRowField
.Position = 1
End With
.AddDataField Recipient.PT(TblNm).PivotFields("Amount Due"), "Sum Of Amount Due", xlSum
With .PivotFields("CCY")
.Orientation = xlColumnField
.Position = 1
End With
End With
With PivotStRng
.Offset(-1, 0) = TblLbl
.Offset(0, 1) = "Currency"
.Offset(1, 0) = "Fund"
End With
LastCol = PivotStRng.Offset(1, 0).End(xlToRight).Column
LastRow = PivotStRng.End(xlDown).Row
Set PivotFinish = Cells(LastRow, LastCol)
Application.Names.Add TblNm, Range(PivotStRng.Offset(2, 1), PivotFinish)
With Application
.ScreenUpdating = ScreenUpdate
.DisplayAlerts = True
End With
End Function
Bookmarks