vba 运行时错误 1004：应用程序定义或对象定义错误

Run Time Error 1004: Application-Defined or Object Defined Error

提问by Mohit

I am trying to record a macro which will create pivot chart out of excel data and here is the code that has been recorded:

我正在尝试录制一个宏，该宏将从 excel 数据中创建数据透视图，这里是已录制的代码：

Sub chart1()
 '
 ' chart1 Macro
 '

 '
Range("E1:F11").Select
Sheets.Add
In Debugger, code within the **** **** is shown in Yellow color
***** ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "data!R1C5:R11C6", Version:=xlPivotTableVersion12).CreatePivotTable _
    TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion12 ********
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("question1")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("answer1")
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("answer1"), "Count of answer1", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("answer1")
    .Orientation = xlPageField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("answer1").Orientation = _
    xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields("answer1")
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A:$D")
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A:$D")
ActiveChart.ChartType = xlColumnClustered
End Sub

Why am I getting the Run Time Error 1004: Application-Defined or Object Defined Error when i try to run this macro ?

当我尝试运行此宏时，为什么会出现运行时错误 1004：应用程序定义或对象定义错误？

Thanks in advance..

提前致谢..

回答by Larry

Since you want the chart to be on a new sheet, you have to change the macro's "Sheet1" to new worksheet's name, The following macro should work for you, I have named the new worksheet as newWs

由于您希望图表位于新工作表上，因此必须将宏的“Sheet1”更改为新工作表的名称，以下宏应该适合您，我已将新工作表命名为 newWs

And fyi, your macro's error message I believe is due to trying to creating 2 pivot table of the same name on "Sheet1" is not allowed.

仅供参考，我认为您的宏的错误消息是由于不允许尝试在“Sheet1”上创建同名的 2 个数据透视表。

He also like to know what to create pivotTable base on Selected Area, so I have done modification to the code.

他也很想知道基于Selected Area创建pivotTable是什么，所以我对代码做了修改。

Edited: I Assume you select 2 columns each time

编辑：我假设您每次选择 2 列

Sub chart1()
 '
 ' chart1 Macro
 '

 '
Dim selectedSheetName As String
Dim newWs As Worksheet
Dim rangeName As String
Dim header1 As String
Dim header2 As String
header1 = ActiveSheet.Cells(1, Selection.Column).Value
header2 = ActiveSheet.Cells(1, Selection.Column + 1).Value
selectedSheetName = ActiveSheet.Name
rangeName = Selection.Address
Set newWs = Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    selectedSheetName & "!" & rangeName, Version:=xlPivotTableVersion12).CreatePivotTable _
    TableDestination:=newWs.Name & "!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion12
newWs.Activate
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields(header1)
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields(header2)
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields(header2), "Count of answer1", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields(header2)
    .Orientation = xlPageField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields(header2).Orientation = _
    xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields(header2)
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(newWs.Name & "!$A:$D")
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(newWs.Name & "!$A:$D")
ActiveChart.ChartType = xlColumnClustered
End Sub