vba - 特定工作表中的 Excel 2010 VBA 数据透视表创建不起作用

Question

我在 Excel 中使用 VBA 创建报告。但是，当我尝试为特定工作表创建数据透视表时，它没有创建，并且显示错误“需要运行时错误‘424’对象”。我在这里发布了我的代码请告诉我问题是什么

Private Sub CommandButton1_Click()
    createPivot
End Sub
Sub createPivot()
    ' Creates a PivotTable report from the table on studentmarks
    ' by using the PivotTableWizard method with the PivotFields
    ' method to specify the fields in the PivotTable.
    Dim objTable As PivotTable, objField As PivotField

    ' Select the sheet and first cell of the table that contains the data.
    ActiveWorkbook.Sheets("studentmarks").Select
    Range("A1").Select


    Set objTable = reports.PivotTableWizard
    ''''Set objTable = Sheet1.PivotTableWizard  // if I give sheet1 instead of reports it is working but every time it is creating new worksheets
    objTable.ColumnGrand = False

    ' Specify a page field.
    Set objField = objTable.PivotFields("subject")
    objField.Orientation = xlPageField

    ' Specify row and column fields.

    Set objField = objTable.PivotFields("name")
    objField.Orientation = xlRowField



    Set objField = objTable.PivotFields("subject")
    objField.Orientation = xlColumnField

    Set objField = objTable.PivotFields("total")
    objField.Orientation = xlDataField



End Sub

我需要在“报告”工作表中创建数据透视表请帮助我..

Answer 1

从你的问题来看，我认为你需要这样的东西。

Dim wsTarget As Worksheet
Dim rngSource As Range
Dim pc As PivotCache
Dim pt As PivotTable
Dim field As PivotField

Set rngSource = Sheets("studentmarks").Range("A1").CurrentRegion
Set wsTarget = Sheets("reports")

wsTarget.Select
For Each pt In wsTarget.PivotTables      
        pt.TableRange2.Clear      
Next pt


Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, rngSource, xlPivotTableVersion14)
Set pt = pc.CreatePivotTable(wsTarget.Range("A1"), "PivotTable1", , xlPivotTableVersion14)

Set field = wsTarget.PivotTables("PivotTable1").PivotFields("subject")
field.Orientation = xlPageField

Set field = wsTarget.PivotTables("PivotTable1").PivotFields("subject")
field.Orientation = xlColumnField

Set field = wsTarget.PivotTables("PivotTable1").PivotFields("name")
field.Orientation = xlRowField

Set field = wsTarget.PivotTables("PivotTable1").PivotFields("total")
field.Orientation = xlDataField

此代码将在报告表内创建一个数据透视表。

希望对你有用