Excel宏开发求助：按A列分类批量生成独立Excel文件

以下是修改后的代码，可实现按A列的ITT1-ITT5分类批量生成独立Excel文件，且不会修改原始主文件：

Sub SplitByITT()
    Dim mainWB As Workbook
    Set mainWB = ThisWorkbook ' 指向当前主工作簿
    Dim savePath As String
    savePath = "Z:\Incent_2022\ORDINARIA\RETAIL-WHS\Andamento\Q4\Andamento\Novembre\"
    Dim ittCategories As Variant
    ittCategories = Array("ITT1", "ITT2", "ITT3", "ITT4", "ITT5") ' 定义所有分类
    
    Dim category As Variant
    For Each category In ittCategories
        ' 复制主工作簿到新工作簿
        mainWB.Sheets.Copy
        Dim newWB As Workbook
        Set newWB = ActiveWorkbook
        
        With newWB.ActiveSheet
            Const FirstRow As Long = 6
            Dim LastRow As Long
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            
            ' 反向遍历删除非当前分类的行
            Dim Row As Long
            For Row = LastRow To FirstRow Step -1
                If .Range("A" & Row).Value <> category Then
                    .Range("A" & Row).EntireRow.Delete
                End If
            Next Row
        End With
        
        ' 生成对应分类的文件名并保存
        Dim fileName As String
        fileName = "And. Inc Q4_" & category & ".xlsm"
        newWB.SaveAs Filename:=savePath & fileName, FileFormat:=52
        newWB.Close SaveChanges:=True
    Next category
End Sub

关键改动说明： #

• 分类数组循环：通过Array("ITT1",...,"ITT5")定义所有需要处理的分类，用For Each循环逐个处理
• 保护原文件：使用mainWB.Sheets.Copy复制原工作簿的所有工作表到新文件，避免直接修改原始数据
• 动态文件名：根据当前分类名拼接文件名，确保每个分类对应独立文件
• 反向删除行：保持原代码中从后往前删除行的逻辑，避免因删除行导致的索引错乱

内容的提问来源于stack exchange，提问作者Stefano Piacente