I am trying to create a Pivot table on a newsheet using dynamic range, but getting an error that says:
我正在尝试使用动态范围在新闻表上创建数据透视表,但收到的错误是:
Run time error: 13 Type Mismatch
运行时错误:13类型不匹配
Googled the error and as per my understanding, the code contains data type that is not matched correctly, but I am not able to figure out where is the error:
谷歌搜索错误,根据我的理解,代码包含不正确匹配的数据类型,但我无法弄清楚错误在哪里:
Using Excel 2016.
使用Excel 2016。
Sub EEE()
Dim PrevSheet As Worksheet
Set PrevSheet = ActiveSheet
Sheets.Add.Name = "Pivottable"
PrevSheet.Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=ActiveSheet.UsedRange, _
Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Pivottable!R3C1", _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion15
Sheets("Pivottable").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Faculty")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("NPS")
.Orientation = xlColumnField
.Position = 1
End With
End Sub
Modified Code - Working
修改后的代码 - 工作
Sub EEE()
Dim rng As Range
Dim pc As PivotCache
Dim pt As PivotTable
Dim ws As Worksheet
Set rng = ActiveSheet.Range("A1").CurrentRegion
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabaseSourceData:=rng.Address)
Set ws = ActiveWorkbook.Worksheets.Add
ws.Name = "NewSheet"
Set pt = pc.CreatePivotTable(TableDestination:=Range("A3"),TableName:="pvttbl")
With pt
.PivotFields("Faculty").Orientation = xlRowField
ActiveSheet.PivotTables("pvttbl").AddDataField ActiveSheet.PivotTable "pvttbl").PivotFields("NPS"), "Count of NPS", xlCount
.PivotFields("NPS").Orientation = xlColumnField
End With
End Sub
1 个解决方案
#1
3
You don't say what line is giving the error. But there's a couple of possible culprits. The line SourceData:=ActiveSheet.UsedRange
says "Hey, go make the PivotTable out of everything you find on the active sheet". Not a good idea. Use the exact range where the data is. Either select a cell where there block of data is and use the CurrentRegion e.g.
你没有说出哪一行给出了错误。但是有几个可能的罪魁祸首。行SourceData:= ActiveSheet.UsedRange说:“嘿,从你在活动工作表上找到的所有内容中制作数据透视表”。不是个好主意。使用数据所在的确切范围。选择存在数据块的单元格并使用CurrentRegion,例如
SourceData:= Activesheet.Range("A1").CurrentRegion
...or even better, turn that SourceData into an Excel Table aka ListObject earlier in the code, and reference that ListObject:
...甚至更好,将SourceData转换为代码中较早的Excel Table又名ListObject,并引用ListObject:
SourceData:= "Table1"
Your code will also fail if you try to run it more than once and you haven't deleted the sheet called "PivotTable" that it created last time.
如果您尝试多次运行它并且您没有删除上次创建的名为“PivotTable”的工作表,您的代码也将失败。
Here's how I would code this up:
以下是我编写代码的方法:
Option Explicit
Sub EEE()
Dim rng As Range
Dim pc As PivotCache
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Set rng = Range("A1").CurrentRegion '<- Change to the address of the top left cell in your data.
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng.Address)
Set ws = ActiveWorkbook.Worksheets.Add
Set pt = pc.CreatePivotTable(TableDestination:=Range("A1"))
With pt
.PivotFields("Faculty").Orientation = xlRowField
.PivotFields("NPS").Orientation = xlColumnField
End With
End Sub
#1
3
You don't say what line is giving the error. But there's a couple of possible culprits. The line SourceData:=ActiveSheet.UsedRange
says "Hey, go make the PivotTable out of everything you find on the active sheet". Not a good idea. Use the exact range where the data is. Either select a cell where there block of data is and use the CurrentRegion e.g.
你没有说出哪一行给出了错误。但是有几个可能的罪魁祸首。行SourceData:= ActiveSheet.UsedRange说:“嘿,从你在活动工作表上找到的所有内容中制作数据透视表”。不是个好主意。使用数据所在的确切范围。选择存在数据块的单元格并使用CurrentRegion,例如
SourceData:= Activesheet.Range("A1").CurrentRegion
...or even better, turn that SourceData into an Excel Table aka ListObject earlier in the code, and reference that ListObject:
...甚至更好,将SourceData转换为代码中较早的Excel Table又名ListObject,并引用ListObject:
SourceData:= "Table1"
Your code will also fail if you try to run it more than once and you haven't deleted the sheet called "PivotTable" that it created last time.
如果您尝试多次运行它并且您没有删除上次创建的名为“PivotTable”的工作表,您的代码也将失败。
Here's how I would code this up:
以下是我编写代码的方法:
Option Explicit
Sub EEE()
Dim rng As Range
Dim pc As PivotCache
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Set rng = Range("A1").CurrentRegion '<- Change to the address of the top left cell in your data.
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng.Address)
Set ws = ActiveWorkbook.Worksheets.Add
Set pt = pc.CreatePivotTable(TableDestination:=Range("A1"))
With pt
.PivotFields("Faculty").Orientation = xlRowField
.PivotFields("NPS").Orientation = xlColumnField
End With
End Sub