excel 从变量数据集创建数据透视表

weylhg0b 于 7个月前发布在其他

关注(0)|答案(1)|浏览(74)

我写了下面的代码从一个变量数据集创建一个数据透视表，显示有多少学生在某个等级内得到了什么范围的分数。然而，它返回一个运行时错误5消息，第8-11行被调试器突出显示。然而，我不能找出出了什么问题
抱歉，如果这是基本的，我是新来的！
（我注解了第15行，因为特定的透视表不需要任何列字段）

Sub create_pivot()
    Dim mysourcedata, mydestination As String
    Dim lr As Long
    
    lr = Sheets("PPM").Range("A1").End(xlDown).Row
    mysourcedata = "PPM!R1C1:R" & lr & "C" & Sheets("PPM").Cells(1, Columns.Count).End(xlToLeft).Column
    mydestination = "Pivots and Graphs!R13C2"
    
    Sheets("Pivots and Graphs").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=mysourcedata, Version:=8).CreatePivotTable _
        TableDestination:=mydestination, TableName:="PivotTable5", _
        DefaultVersion:=8
        
    With ActiveSheet.PivotTables("PivotTable5")
        .PivotFields("Grade").Orientation = xlPageField
        .PivotFields("Range").Orientation = xlRowField
        '.PivotFields("ColumnField").Orientation = xlColumnField
        .PivotFields("Student Number").Orientation = xlDataField
    End With
End Sub

字符串
我试过谷歌搜索，但我看不到一个明确的答案，似乎有多种方式编写的命令在谷歌来相同的结果！

excel

来源：https://stackoverflow.com/questions/77430729/vba-to-create-a-pivot-table-from-a-variable-dataset

1条答案

按热度按时间

uubf1zoe1#

如注解中所述，您可以使用Range对象，而无需创建它们的字符串表示：
举例来说：

Sub create_pivot()
    
    Dim mysourcedata As Range, mydestination As Range
    Dim lr As Long, lc As Long, wb As Workbook
    Dim pc As PivotCache, pt As PivotTable
    
    Set wb = ThisWorkbook 'or activeworkbook for example
    With wb.Sheets("PPM")
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set mysourcedata = .Range("A1", .Cells(lr, lc))
    End With
    
    Set mydestination = wb.Worksheets("Pivots and Graphs").Range("B13")
    
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, _
                SourceData:=mysourcedata, Version:=8)
        
    Set pt = pc.CreatePivotTable(TableDestination:=mydestination, _
                TableName:="PivotTable5", DefaultVersion:=8)
    
    With pt
        .PivotFields("Grade").Orientation = xlPageField
        .PivotFields("Range").Orientation = xlRowField
        '.PivotFields("ColumnField").Orientation = xlColumnField
        .PivotFields("Student Number").Orientation = xlDataField
    End With
End Sub

字符串

赞(0）回复(0）举报 7个月前

我来回答

excel 从变量数据集创建数据透视表

1条答案

相关问题

热门标签

最新问答