Creating a Pivot Table using VBA error

Question

I have this code below, but the Set PT_Cache line is raising a Type mismatch error.

Anyone knows why?

Sub Create_Pivot_Table()
Dim wsData As Worksheet, wsPT As Worksheet
Dim PT_Cache As PivotCache
Dim PT As PivotTable
Dim LastRow As Long

With ThisWorkbook
Set wsData = .Worksheets("Data")
Set wsPT = .Worksheets("Pivot Table")
End With

LastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row

Set PT_Cache = ThisWorkbook.PivotCaches.Create(xlDatabase, wsData.Range("A1:O" & LastRow))

Set PT = PT_Cache.CreatePivotTable(wsPT.Range("D5"), "Pivot_Table_Test")

Set PT = Nothing
Set PT_Cache = Nothing
Set wsData = Nothing
Set wsPT = Nothing
Exit Sub

End Sub

what is the value of LastRow ? LastRow should also be LastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row — Shai Rado
– Shai Rado, Commented Apr 12, 2017 at 18:35
I fixed this part and this was not the problem, the "set PT_Cache" still got the error.... — Xuan Zhou
– Xuan Zhou, Commented Apr 12, 2017 at 18:37
@ShaiRado Yes! I just tried! It works! But I'm keep running into problems as I adding more code, I'm able to create a empty pivot table with your code, after I added some values field, it tells me that I have error with the previous code.... — Xuan Zhou
– Xuan Zhou, Commented Apr 13, 2017 at 17:10
Then you should open a new post, with your modified code, and describe what you are trying to achieve. — Shai Rado
– Shai Rado, Commented Apr 13, 2017 at 17:12

Shai Rado · Accepted Answer · 2017-04-12 18:48:43Z

Try the code below, I've added 2 options to set the PivotCache, try one and comment the other (or vise versa), see which one works for you (both worked when I tested it with my dummy data)

Code

Option Explicit

Sub Create_Pivot_Table()

Dim wsData As Worksheet, wsPT As Worksheet
Dim PT_Cache    As PivotCache
Dim PT          As PivotTable
Dim PRng        As Range
Dim LastRow     As Long

With ThisWorkbook
    Set wsData = .Worksheets("Data")
    Set wsPT = .Worksheets("Pivot Table")
End With

LastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
MsgBox LastRow ' <-- confirm value

Set PRng = wsData.Range("A1:O" & LastRow)

' option 1: Set the Pivot Cache
Set PT_Cache = ThisWorkbook.PivotCaches.Create(xlDatabase, PRng)

' option 2: Set the Pivot Cache
Set PT_Cache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRng.Address(True, True, xlR1C1, True))

' set the Pivot Table
Set PT = wsPT.PivotTables.Add(PivotCache:=PT_Cache, TableDestination:=wsPT.Range("D5"), TableName:="Pivot_Table_Test")

Set PT = Nothing
Set PT_Cache = Nothing
Set wsData = Nothing
Set wsPT = Nothing
Exit Sub

End Sub

Collectives™ on Stack Overflow

Creating a Pivot Table using VBA error

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related