英文:
Pivot Table Count Macro
问题
I'm here to provide you with the translation of your content:
我正在尝试创建一个包含数据透视表的宏,但我无法使其正常工作...当我录制宏时,一切都正常,但当我再次执行它时,它无法产生预期的结果...
这是原始数据的示例:
这是我需要的结果:
我需要宏按级别具有多列,并计算每个级别的数量,但我无法使用宏来复制这个功能...
这是我尝试过的代码:
NomHoja = ActiveSheet.Name
Range("A1:B11").Select
Sheets.Add
NomHojaTD = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'" & NomHoja & "'!R1C1:R11C2", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:= "'" & NomHojaTD & "'!R3C1", TableName:="Tabla dinámica3", _
DefaultVersion:=xlPivotTableVersion15
Sheets(NomHojaTD).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Type ")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Level")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Tabla dinámica3").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica3").PivotFields("Level"), "Cuenta de Level", _
xlCount
但这是我实际得到的结果...
似乎我无法同时将“Level”用作行和计数...
(Note: I've removed the HTML character codes like "
for clarity in the translation.)
英文:
I´m trying to do a Macro that includes a Pivot Table, but I´m unable to make it work... When I record the macro all works OK, but when I execute it again, it does not deliver the expected results...
This is an Example of Original Data
This is what I need
I need the macro to have multiple Colums by Level, and Count each Level
But I´m unable to reproduce that with a macro...
Here is the code I tried:
NomHoja = ActiveSheet.Name
Range("A1:B11").Select
Sheets.Add
NomHojaTD = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'" & NomHoja & "'!R1C1:R11C2", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="'" & NomHojaTD & "'!R3C1", TableName:="Tabla dinámica3", _
DefaultVersion:=xlPivotTableVersion15
Sheets(NomHojaTD).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Type ")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Level")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Tabla dinámica3").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica3").PivotFields("Level"), "Cuenta de Level", _
xlCount
But this is what I get instead...
This is what I get...
Seems I cannot use "Level" as Row and Ccount at the same time...
答案1
得分: 0
这对我有效 - 似乎关键在于添加字段的顺序...
英文:
This works for me - it seems to matter what order you add the fields in...
Sub Tester()
Dim wb As Workbook, pc As PivotCache, pt As PivotTable
Dim wsData As Worksheet, wsPT As Worksheet, rngData As Range
Set wsData = ActiveSheet
Set rngData = wsData.Range("A1").CurrentRegion 'the data for the PT
Set wb = wsData.Parent 'parent workbook
Set wsPT = wb.Worksheets.Add(after:=wsData)
'create the pivot cache
Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=rngData, Version:=xlPivotTableVersion15)
'create the pivot table
Set pt = pc.CreatePivotTable(TableDestination:=wsPT.Range("A3"))
With pt.PivotFields("Type")
.Orientation = xlRowField
.Position = 1
End With
pt.AddDataField pt.PivotFields("Level"), _
"Cuenta de Level", xlCount
With pt.PivotFields("Level")
.Orientation = xlColumnField
.Position = 1
End With
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论