Excel Vba，创建数据透视表，按值前10位自动显示，错误1004 [英] Excel Vba, create pivot table, autoshow by value top 10, error 1004

问题描述

我正在使用excel vba创建的数据透视表创建一个十大值，但似乎我收到一个错误

I'm trying to create a top 10 value with the pivot table created with excel vba but it seems like I'm getting an error

运行时错误'1004'：

Run-time Error '1004':

应用程序定义或对象定义的错误

Application-defined or object defined error

这是代码，在我通过互联网搜索后似乎找不到问题。

Here is the code, I can't seems to find the problem after I search through the internet

Sub PivotGenerate()

'declaration
Dim sht As Worksheet
Dim pvt As PivotTable
Dim pvtFld As PivotField
Dim pvtCache As PivotCache
Dim StartPvt As String
Dim SrcData As String
Dim RangeInput As Range

'Input value
Set RangeInput = Application.InputBox(Prompt:= _
        "Please Select Name range", _
        Title:="InputBox Method", Type:=8)

'Stop Updating
Application.ScreenUpdating = False

'data range for pivot
SrcData = ActiveSheet.Name & "!" & RangeInput.Address(ReferenceStyle:=xlR1C1)

'error handling for new worksheet
Application.DisplayAlerts = False

On Error Resume Next
ThisWorkbook.Sheets("PivotTable").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'create new worksheet
Set sht = Worksheets.Add(After:=Sheets(Worksheets.Count))
sht.Name = "PivotTable"

'Pivot Table location
StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)

'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable(TableDestination:=StartPvt, TableName:="Top 10 Commitment Value")

'Set active
Set pvt = ActiveSheet.PivotTables("Top 10 Commitment Value")

'Classic View
pvt.InGridDropZones = True
pvt.RowAxisLayout xlTabularRow

'Remove Subtotal
With pvt
   For Each pvtFld In .PivotFields
   pvtFld.Subtotals(1) = False
Next pvtFld
End With

'Row Labels
pvt.PivotFields("CUST Name").Orientation = xlRowField
pvt.PivotFields("CUST Name").Position = 1

'Data Value
pvt.AddDataField pvt.PivotFields("Commitment RM"), "Sum of Commitment RM", xlSum
pvt.AddDataField pvt.PivotFields("OS BALANCE RM (On BS)"), "Sum of OS BALANCE RM (On BS)", xlSum

pvt.PivotFields("CUST Name").AutoShow Type:=xlAutomatic, Range:=xlTop, Count:=5, Field:="Sum of Commitment RM"


'Activate Updating
Application.ScreenUpdating = True

End Sub

调试在代码中突出显示了这一行

The debugging highlighted this line in the code

pvt.PivotFields（CUST Name）。自动显示类型：= xlAutomatic，范围：= xlTop，计数：= 5，字段：=承诺RM总和

pvt.PivotFields("CUST Name").AutoShow Type:=xlAutomatic, Range:=xlTop, Count:=5, Field:="Sum of Commitment RM"

数据透视表被管理生成，但前十行错误

The Pivot Table is manage to be generated but the Top 10 line gave error

推荐答案

感谢来自 Doug Glancy 的帮助，这是我从宏记录器获得的

Thanks for the help from Doug Glancy, This is what I get from the Macro Recorder

ActiveSheet.PivotTables("PivotTableTop10").PivotFields("CUST NAME"). _ PivotFilters.Add2 Type:=xlTopCount, DataField:=ActiveSheet.PivotTables( _ "PivotTableTop10").PivotFields("Sum of Commitment RM"), Value1:=10

这篇关于Excel Vba，创建数据透视表，按值前10位自动显示，错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！