VBA为另一个工作表中的每个过滤器数据创建表

Sub CreateTablesForFilters()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim rngData As Range
    Dim cell As Range
    Dim filterValue As String
    Dim tableName As String
    
    ' 设置源工作表和目标工作表
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    
    ' 设置数据范围
    Set rngData = wsSource.Range("A1:D100")
    
    ' 遍历每个过滤器值
    For Each cell In wsSource.Range("E1:E100").Cells
        If cell.Value <> "" Then
            filterValue = cell.Value
            tableName = "Table_" & filterValue
            
            ' 创建新的表格
            With wsDest.PivotTables.Add(SourceType:=xlDatabase, SourceData:=rngData, TableDestination:=wsDest.Cells(1, 1), TableName:=tableName)
                .RowFields.Add Field:=wsSource.Range("A:A"), Name:="Product"
                .ColumnFields.Add Field:=wsSource.Range("B:B"), Name:="Category"
                .DataFields.Add Field:=wsSource.Range("C:C"), Name:="Sales", Function:=xlSum
            End With
        End If
    Next cell
End Sub