创建数据透视表时键入不匹配错误

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

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

SourceData:= Activesheet.Range("A1").CurrentRegion