excel2016更改数据透视表索引

sz216

请教大牛：自己用excel2016录制的数据敌视表宏，第次使用都行更改工作表名，要求1：能不能更改一下数据透视表的索引。要求2：数据源删除或添加的时候自动涮新。
Sub 透视表13()
'
' 透视表13 宏
'

'
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R90C17", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet2!R3C1", TableName:="数据透视表3", DefaultVersion:=6
    Sheets("Sheet2").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("商品产地")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("客户名称")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("商品名称")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("数量")
        .Orientation = xlRowField
        .Position = 4
    End With
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("单位")
        .Orientation = xlRowField
        .Position = 5
    End With
    ActiveSheet.PivotTables("数据透视表3").ShowDrillIndicators = False
    With ActiveSheet.PivotTables("数据透视表3")
        .ColumnGrand = False
        .RowGrand = False
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    ActiveSheet.PivotTables("数据透视表3").PivotFields("单据类型").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("单号").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("单据状态").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("单据日期").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("成本单价").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("部门名称").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("商品产地").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("客户名称").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("商品名称").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("数量").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("单位").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("序号").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("商品编号").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("单价").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("金额").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("毛利").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("数据透视表3").PivotFields("交货地点").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveWindow.SmallScroll Down:=-9
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("商品产地")
        .PivotItems("").Visible = False
    End With
    ActiveWindow.SmallScroll Down:=-30
    Columns("B:B").ColumnWidth = 13.5
    Columns("B:B").ColumnWidth = 17.13
    Columns("A:A").ColumnWidth = 12
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=66
    Sheets("Sheet2").Select
    ActiveWindow.SmallScroll Down:=-15
    Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    Range("B16").Select
    ActiveWindow.SmallScroll Down:=-30
    ActiveSheet.PivotTables("数据透视表3").PivotFields("商品产地").Orientation = xlHidden
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("商品产地")
        .Orientation = xlRowField
        .Position = 5
    End With
    ActiveSheet.PivotTables("数据透视表3").PivotFields("客户名称").Orientation = xlHidden
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("客户名称")
        .Orientation = xlRowField
        .Position = 5
    End With
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("数据透视表3"), "客户名称"). _
        Slicers.Add ActiveSheet, , "客户名称", "客户名称", 190.5, 536.25, 144, 176.25
    ActiveSheet.Shapes.Range(Array("客户名称")).Select
    ActiveSheet.Shapes("客户名称").IncrementLeft -34.5
    ActiveSheet.Shapes("客户名称").IncrementTop -126
    ActiveSheet.Shapes("客户名称").ScaleHeight 2.7872340426, msoFalse, _
        msoScaleFromTopLeft
    Range("B9").Select
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("商品产地")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("客户名称")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("数据透视表3").PivotFields("商品名称")
        .Orientation = xlRowField
        .Position = 3
    End With
    Columns("D:D").ColumnWidth = 7.5
End Sub