|
- Sub test()
- With Application
- .ScreenUpdating = False
- .DisplayAlerts = False
- .EnableEvents = False
- .Calculation = xlCalculationManual
- End With
- Sheet1.Activate
- If Range("a1").Value <> "货号" Then
- Rows("1:4").Delete Shift:=xlUp
- Columns("C:P").Delete Shift:=xlToLeft
- Range("D:D,A:A").Delete Shift:=xlToLeft
- With Columns("B:B")
- .Insert Shift:=xlToRight
- .Insert Shift:=xlToRight
- .Insert Shift:=xlToRight
- End With
- Columns("B:D").NumberFormatLocal = "G/通用格式"
- Range("B2").FormulaR1C1 = "=LEFT(RC[-1],1)"
- Range("C2").FormulaR1C1 = "=MID(RC[-2],2,1)"
- Range("D2").FormulaR1C1 = "=MID(RC[-3],3,1)"
- Range("B2:D2").AutoFill Destination:=Range("B2:D737")
- Range("B1").FormulaR1C1 = "品牌"
- Range("C1").FormulaR1C1 = "年度"
- Range("D1").FormulaR1C1 = "季度"
- Columns("A:E").EntireColumn.AutoFit
- End If
- With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange.Address)
- With .CreatePivotTable(TableDestination:="")
- With .PivotFields("品牌")
- .Orientation = xlRowField
- .Position = 1
- End With
- With .PivotFields("年度")
- .Orientation = xlRowField
- .Position = 2
- End With
- With .PivotFields("季度")
- .Orientation = xlRowField
- .Position = 3
- End With
- .AddDataField .PivotFields("合计"), "求和项:合计", xlSum
- .TableStyle2 = ""
- .RowAxisLayout xlTabularRow
- .ShowDrillIndicators = False
- End With
- End With
- With Application
- .ScreenUpdating = True
- .DisplayAlerts = True
- .EnableEvents = True
- .Calculation = xlCalculationAutomatic
- End With
- MsgBox "OK"
- End Sub
复制代码 |
|