[原创]请教透视表如何更改SourceData？

sandwich

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        sourceDataRg .CreatePivotTable TableDestination:="", TableName:= _
        TableName, DefaultVersion:=xlPivotTableVersion10

运行时产生错误：运行时错误13，类型不匹配

把sourceDataRg 改成"测试用例!R2C1:R420C22" 运行没有问题。

请教是什么原因？如何才能使用sourceDataRg 的方式？

Sub 生成数据透视表(SheetName As String)
    Dim TableName As String
    Dim sourceDataRg As Range
   
    TableName = "数据透视表"
    Sheets(SheetName).Activate
    Sheets(SheetName).Cells(2, 1).Select
    Set sourceDataRg = Range(Cells(2, 1), Cells(420, 22))
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        sourceDataRg ).CreatePivotTable TableDestination:="", TableName:= _
        TableName, DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.Name = TableName
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
   
    With ActiveSheet.PivotTables(TableName).PivotFields("路径")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables(TableName).PivotFields("级别")
        .Orientation = xlColumnField
        .Position = 1
    End With
   
    ActiveSheet.PivotTables(TableName).AddDataField ActiveSheet.PivotTables(TableName _
        ).PivotFields("级别"), "计数项:级别", xlCount
   
        With ActiveSheet.PivotTables(TableName).PivotFields("级别")
        .Orientation = xlColumnField
        .Position = 1
    End With
   
    ActiveWorkbook.ShowPivotTableFieldList = False
   
End Sub

[此贴子已经被作者于2007-3-3 15:36:21编辑过]

向您问好

可能是R2C1:R420C22格式是动态引用单元格原因吧

auluola

ActiveWorkbook.PivotCaches.Add xlDatabase, sourceDataRg

wxhnr

sourceDataRg.Address