使用VBA错误创建一个Pivot表

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

我下面有这段代码，但是Set PT_Cache行引发了类型不匹配错误。

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

1 个解决方案

#1

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