我正在尝试编写一些VBA,它将在新选项卡中构建数据透视表 . 当我运行代码时,我在创建数据透视表缓存时遇到类型不匹配错误 .
代码在下面,任何帮助将不胜感激...希望重新审视它可以发现我所缺少的
Sub makeAPivotTable()
Dim sSheet, sSheet2 As Worksheet 'sSheet is where the data is, sSheet2 is where the pivot will be built
Dim cCache As PivotCache
Dim ptTable As PivotTable
Dim rRange As Range
Dim rLastRow, cLastColumn As Long
' Insert the new sheet for the pivot table to reside
Application.DisplayAlerts = False
Sheets.Add Before:=ActiveSheet
ActiveSheet.name = "PivotTable"
Application.DisplayAlerts = True
Set sSheet2 = Worksheets("PivotTable")
Set sSheet = Worksheets("Interactions db")
' define the range (the data that you want to put into the pivot table
rLastRow = sSheet.Cells(Rows.Count, 1).End(xlUp).Row
cLastColumn = sSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set rRange = sSheet.Cells(1, 1).Resize(rLastRow, cLastColumn)
' create the cache for the pivot table
Set cCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=rRange). _
CreatePivotTable(TableDestination:=sSheet2.Cells(2, 2), _
TableName:="SalesPivotTable")
' insert the blank table
Set ptTable = cCache.CreatePivotTable _
(TableDestination:=sSheet2.Cells(1, 1), TableName:="SalesPivotTable")
'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("customer")
.Orientation = xlRowField
.Position = 1
End With
'Insert Column Fields
'With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Interaction Type")
'.Orientation = xlColumnField
'.Position = 1
'End With
'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("interactionType")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0"
.name = "Person "
End With
' do some formatting
End Sub
1 回答
尝试下面的代码(代码中的解释作为注释)