用VBA生成透视表，在Office365下运行

lilaye

求大神看过来
日常导出的数据，需要整理成透视表后再使用。 sample数据和所需要的透视已经放在附件里了。

主要是要先把最下面1行或n行的有黄色填充的总计行删除，再进行透视。

另外，其实之前自己拼拼凑凑过一段下面这段，在配合自己手动删除总计行后可以勉强能用，但是公司后来换了office365, 就直接变成报错无法使用了，也不知道这是否和office的版本有影响


Sub 生成数据透视表()
Dim strSourceData As String, iR As Integer, iC As Integer

iR = Sheets("Sheet1").Range("A65536").End(xlUp).Row     
iC = Sheets("Sheet1").Range("IV1").End(xlToLeft).Column   


strSourceData = "Sheet1!R1C1:R" & iR & "C" & iC         

'Sheets("5").Rows.Clear                        

pdata1 = ActiveSheet.Name & "!R1C1:R" & MaxRow & "C9"
Sheets.Add
pdata2 = ActiveSheet.Name & "!R3C1"


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
strSourceData, Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:=ActiveSheet.Name & "!R3C1", TableName:="5", DefaultVersion:= _
xlPivotTableVersion15

Cells(3, 1).Select
ActiveSheet.PivotTables("5").PivotFields("Vendor Invoice").Subtotals(1) = False
ActiveSheet.PivotTables("5").PivotFields("ASN Invoice No.").Subtotals(1) = False
ActiveSheet.PivotTables("5").PivotFields("Delivery").Subtotals(1) = False
ActiveSheet.PivotTables("5").PivotFields("Brand").Subtotals(1) = False
ActiveSheet.PivotTables("5").PivotFields("Site").Subtotals(1) = False
ActiveSheet.PivotTables("5").PivotFields("SLoc").Subtotals(1) = False
ActiveSheet.PivotTables("5").PivotFields("PO Year").Subtotals(1) = False
ActiveSheet.PivotTables("5").PivotFields("PO Season").Subtotals(1) = False
ActiveSheet.PivotTables("5").RowAxisLayout xlTabularRow

With ActiveSheet.PivotTables("5").PivotFields("Vendor Invoice")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("5").PivotFields("ASN Invoice No.")
    .Orientation = xlRowField
    .Position = 2
End With
With ActiveSheet.PivotTables("5").PivotFields("Delivery")
    .Orientation = xlRowField
    .Position = 3
End With
With ActiveSheet.PivotTables("5").PivotFields("Brand")
    .Orientation = xlRowField
    .Position = 4
End With
With ActiveSheet.PivotTables("5").PivotFields("Site")
    .Orientation = xlRowField
    .Position = 5
End With
With ActiveSheet.PivotTables("5").PivotFields("SLoc")
    .Orientation = xlRowField
    .Position = 6
End With

With ActiveSheet.PivotTables("5").PivotFields("PO Year")
    .Orientation = xlRowField
    .Position = 7
End With
With ActiveSheet.PivotTables("5").PivotFields("PO Season")
    .Orientation = xlRowField
    .Position = 8
End With

ActiveSheet.PivotTables("5").RowAxisLayout xlTabularRow  
ActiveSheet.PivotTables("5").InGridDropZones = True

ActiveSheet.PivotTables("5").AddDataField ActiveSheet.PivotTables("5" _
        ).PivotFields("Qty"), "求和：Qty", xlSum

ActiveSheet.PivotTables("5").AddDataField ActiveSheet.PivotTables("5" _
        ).PivotFields("Line Amount"), "求和：Line Amount", xlSum

End Sub

3190496160

1.一定要透视表吗？代码做成汇总表的效果行吗？根本看不出的表格结构特点，3，你需要如何汇总，根据哪几个字段汇总