VBA：创建数据透视表时出错 [英] VBA: Error on creating a pivot table

问题描述

我正在创建一个VBA宏。除了别的以外，它创建一个数据透视表：

  ActiveWorkbook.PivotCaches.Add（SourceType：= xlDatabase，SourceData：= Sheets 工作）。UsedRange）.CreatePivotTable TableDestination：=，TableName：=HDPivotTable，DefaultVersion：= xlPivotTableVersion10 
  

这是有效的，但是当我从Excel电子表格中删除所有数据并将其替换为相同结构的新数据时，我在上述行中收到错误：

运行时错误'13'：类型不匹配

你能帮助我吗？

EDITED：

在应用Scott的建议后，我收到另一个错误：

  With Worksheets（Working）
 lRow = .Range（A& .Rows.Count）.End（xlUp） .Row 
 lColumn = .Range（A& .Columns.Count）.End（xlToLeft）.Column 
 ActiveWorkbook.PivotCaches.Add（SourceType：= xlDatabase，SourceData：=。细胞（1，1），细胞（1Row，1Colu mn）））。CreatePivotTable TableDestination：=，TableName：=HDPivotTable，DefaultVersion：= xlPivotTableVersion10 
 End with 
 
 ActiveSheet.Name =HD Pivot
 ActiveSheet .PivotTableWizard TableDestination：= ActiveSheet.Cells（1，1）
 ActiveSheet.PivotTables（HDPivotTable）。AddFields RowFields = =位置代码
  

错误是：
运行时错误'1004'：数据透视表类的AddFields方法失败

解决方案

最可能导致错误的是使用 UsedRange 。虽然是一个漂亮的工具，但它也有其缺点，特别是因为您正在替换数据。

例如，如果新数据具有较少的列，那么旧的数据，或更少的行， UsedRange 将很可能反映了具有数据的旧范围。此外，根据您在工作表中所做的工作，可能会有单元格包含您无法用肉眼看到的格式或其他字符，这将影响 UsedRange

如果您在代码中进行以下调整，那么每次都应该运行。

  Dim ws as Worksheet 
设置ws =工作表（工作）
 
 Dim lRow as Long，lColumn as Long 
 
'假设数据以A1 
开头的连续行/列的表与ws 
 lRow = .Range（A& .Rows.Count）.End（xlup）.Row 
 lColumn = .Cells 1，.Columns.Count）.End（xltoLeft）.Column 
 End with 
 
 ActiveWorkbook.PivotCaches.Add（SourceType：= xlDatabase，SourceData：= ws.Range（ws.Cells 1,1），ws.Cells（lRow，lColumn））... 
  

I am creating a VBA macro. Among other things, it makes a pivot table:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Sheets("Working").UsedRange).CreatePivotTable TableDestination:="", TableName:="HDPivotTable", DefaultVersion:=xlPivotTableVersion10

This works, but when I delete all data from the Excel spreadsheet and replace it with new data of the same structure, I get an error on the above line:

Run-time error '13': Type mismatch

Could you please help me on this?

Thanks.

EDITED:

After applying Scott's suggestion, I get another error:

With Worksheets("Working")
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    lColumn = .Range("A" & .Columns.Count).End(xlToLeft).Column
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=.Range(.Cells(1, 1), .Cells(lRow, lColumn))).CreatePivotTable TableDestination:="", TableName:="HDPivotTable", DefaultVersion:=xlPivotTableVersion10
End With

ActiveSheet.Name = "HD Pivot"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)
ActiveSheet.PivotTables("HDPivotTable").AddFields RowFields:="Location Code"

The error is: Run-time error '1004': AddFields method of PivotTable class failed

解决方案

Most likely what is causing the error is the use of UsedRange. While a nifty tool, it also has its drawbacks, especially since you are now replacing data.

For example, if the new data has less columns then the old data, or even less rows, the UsedRange will most likely reflect the old range with data. Furthermore, depending upon what you have been doing with your worksheet there may be cells that contain formatting or other characters that you cannot see with the naked-eye, that will affect the UsedRange.

If you make the following adjustments in your code, it should work every time.

Dim ws as Worksheet
Set ws = Worksheets("Working")

Dim lRow as Long, lColumn as Long

'assumes data table of contiguous rows/columns starting in A1
With ws
    lRow = .Range("A" & .Rows.Count).End(xlup).Row
    lColumn = .Cells(1, .Columns.Count).End(xltoLeft).Column
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ws.Range(ws.Cells(1,1),ws.Cells(lRow,lColumn)) ...

这篇关于VBA：创建数据透视表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！