Private Sub CommandButton1_Click()

tsb_sourcesheetname = "sheet1"
tsb_tablename = "工程量透视表"
tsb_sheetname = "数据统计"
tsb_headname = "灯具全名称"
tsb_sum = "数量"

'
'删除全部的透视表
'---------------------------------------------
    Dim oPT As PivotTable
        With Sheets(tsb_sheetname)
            For Each oPT In .PivotTables
                With oPT
                    .TableRange2.Delete
                End With
            Next
        End With
'---------------------------------------------


'直接赋值给透视表名称，直接删除
' Sheets(tsb_sheetname).PivotTables(tsb_tablename).TableRange2.Delete
    
    
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        tsb_sourcesheetname & "!R2C8:R108C9", Version:=6).CreatePivotTable TableDestination:= _
        tsb_sheetname & "!R3C1", TableName:=tsb_tablename, DefaultVersion:=6
    Sheets(tsb_sheetname).Select
    Sheets(tsb_sheetname).Cells(3, 1).Select
    
    Sheets(tsb_sheetname).PivotTables(tsb_tablename).CompactLayoutRowHeader = tsb_headname
    
    With ActiveSheet.PivotTables(tsb_tablename).PivotFields(tsb_headname)
        .Orientation = xlRowField
        .Position = 1
        .PivotItems("").Visible = False
    End With
    With ActiveSheet.PivotTables(tsb_tablename).PivotFields(tsb_sum)
        .Orientation = xlRowField
        .Position = 2
    End With
    
    ActiveSheet.PivotTables(tsb_tablename).AddDataField ActiveSheet.PivotTables(tsb_tablename _
        ).PivotFields(tsb_sum), "求和项:数量", xlSum
        
    ActiveSheet.PivotTables(tsb_tablename).PivotFields("求和项:数量").Caption = "总计"
            
    ActiveSheet.PivotTables(tsb_tablename).PivotSelect tsb_headname & "[All]", xlLabelOnly + _
        xlFirstRow, True
    ActiveSheet.PivotTables(tsb_tablename).PivotCache.Refresh
     
    
End Sub