我自己想了个办法 将要进行数据透视的源区域定义动态的名称database1 database1=OFFSET(sheet1!$A$1,0,0,COUNTA(sheet1!$A:$A),4) 将database1放入sourcedata即可. Sub 宏3() ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "dababase1").CreatePivotTable TableDestination:="", TableName:= _ "数据透视表3" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("数据透视表3").SmallGrid = False ActiveSheet.PivotTables("数据透视表3").AddFields RowFields:="商品编码" ActiveSheet.PivotTables("数据透视表3").PivotFields("签收数量").Orientation = xlDataField End Sub
|