使用VBA创建表或从具有相同数据的多个选项卡中创建数据透视表(不能PowerPivot)

Sub CreateTable()
    Dim ws As Worksheet
    Dim rng As Range
    
    ' 创建新工作表
    Set ws = ThisWorkbook.Sheets.Add
    
    ' 定义表的范围
    Set rng = ws.Range("A1:C5")
    
    ' 设置表头
    rng.Cells(1, 1).Value = "姓名"
    rng.Cells(1, 2).Value = "年龄"
    rng.Cells(1, 3).Value = "性别"
    
    ' 设置数据
    rng.Cells(2, 1).Value = "张三"
    rng.Cells(2, 2).Value = 25
    rng.Cells(2, 3).Value = "男"
    
    ' 设置格式
    rng.Font.Bold = True
    rng.Borders.LineStyle = xlContinuous
    
    ' 自动调整列宽
    rng.EntireColumn.AutoFit
End Sub

Sub CreatePivotTableFromTabs()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pc As PivotCache
    
    ' 创建新工作表
    Set ws = ThisWorkbook.Sheets.Add
    
    ' 定义数据源范围
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:C10")
    
    ' 创建数据透视表缓存
    Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, rng)
    
    ' 在新工作表上创建数据透视表
    Set pt = ws.PivotTables.Add(pc, ws.Range("A1"), "PivotTable1")
    
    ' 设置行、列和值
    pt.PivotFields("姓名").Orientation = xlRowField
    pt.PivotFields("性别").Orientation = xlColumnField
    pt.PivotFields("年龄").Orientation = xlDataField
    
    ' 设置格式
    pt.TableRange2.Font.Bold = True
    pt.TableRange2.Borders.LineStyle = xlContinuous
    
    ' 自动调整列宽
    pt.TableRange2.EntireColumn.AutoFit
End Sub