[求助] 录制的宏,为何不能运行(制作数据透视表)

L2MINGJING

各位老师:

     我录制了一段制作数据透视表宏 (指定将数据透视表放在工作表名(进度表), 但测试不能运行, 请高手指点,到底应如何调试.

不胜感激之至!

谢谢

 Private Sub CommandButton1_Click()
    Range("F6").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "生产明细!R1C1:R10000C13").CreatePivotTable TableDestination:= _
        "[产能分析.xls]进度表!R3C1", TableName:="数据透视表1", DefaultVersion:= _
        xlPivotTableVersion10
    ActiveSheet.PivotTables("数据透视表1").AddFields RowFields:=Array("批号", "工序", "数据")
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("FZ_SL")
        .Orientation = xlDataField
        .Caption = "发出"
        .Position = 1
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("JH_SL")
        .Orientation = xlDataField
        .Caption = "收回"
        .Position = 2
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("欠数")
        .Orientation = xlDataField
        .Caption = "欠收"
        .Function = xlSum
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("C3").Select
    With ActiveSheet.PivotTables("数据透视表1").DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    Columns("C:E").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

TwDWGvCQ.rar (74.06 KB, 下载次数: 100)

2006-11-20 17:18 上传

点击文件名下载附件
[求助] 录制的宏,为何不能运行(制作数据透视表)

Aeolian-Vox

Private Sub CommandButton1_Click()
    Range("F6").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "生产明细!R1C1:R10000C13").CreatePivotTable TableDestination:= _
        "进度表!R3C1", TableName:="数据透视表1", DefaultVersion:= _
        xlPivotTableVersion10
    Sheet18.PivotTables("数据透视表1").AddFields RowFields:=Array("批号", "工序", "数据")
    With Sheet18.PivotTables("数据透视表1").PivotFields("FZ_SL")
        .Orientation = xlDataField
        .Caption = "发出"
        .Position = 1
        .Function = xlSum
    End With
    With Sheet18.PivotTables("数据透视表1").PivotFields("JH_SL")
        .Orientation = xlDataField
        .Caption = "收回"
        .Position = 2
        .Function = xlSum
    End With
    With Sheet18.PivotTables("数据透视表1").PivotFields("欠数")
        .Orientation = xlDataField
        .Caption = "欠收"
        .Function = xlSum
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    With Sheet18.PivotTables("数据透视表1").DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    With Sheet18.Columns("C:E")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Sheet18.Columns("C:E")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

L2MINGJING

非常感谢老师的指点

L2MINGJING

各位老师:

    如下附蓝色字体的代码（DefaultVersion:= _xlPivotTableVersion10）
为何在EXCEL 2000版本中不能运行, 出现提示为"编译错误：“未找到命名参数”应如何调试。

请老师们指点。

Private Sub CommandButton1_Click()
    Range("F6").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "生产明细!R1C1:R10000C13").CreatePivotTable TableDestination:= _
        "进度表!R3C1", TableName:="数据透视表1", DefaultVersion:= _
        xlPivotTableVersion10
    Sheet18.PivotTables("数据透视表1").AddFields RowFields:=Array("批号", "工序", "数据")
    With Sheet18.PivotTables("数据透视表1").PivotFields("FZ_SL")
        .Orientation = xlDataField
        .Caption = "发出"
        .Position = 1
        .Function = xlSum
    End With
    With Sheet18.PivotTables("数据透视表1").PivotFields("JH_SL")
        .Orientation = xlDataField
        .Caption = "收回"
        .Position = 2
        .Function = xlSum
    End With
    With Sheet18.PivotTables("数据透视表1").PivotFields("欠数")
        .Orientation = xlDataField
        .Caption = "欠收"
        .Function = xlSum
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    With Sheet18.PivotTables("数据透视表1").DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    With Sheet18.Columns("C:E")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Sheet18.Columns("C:E")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub