excel - 使用 VBA 在 Excel 中将主电子表格拆分为多个工作表

Question

(抱歉，我是新来的，所以请多多包涵哈哈)所以我从 YouTube 教程 (https://www.youtube.com/watch?v=5bOFNsdHiPk&t=326s) 中获得了这段代码，我希望能得到进一步的帮助...

Sub SplitandFilterSheet()
'Step 1 - Name your ranges and Copy sheet
'Step 2 - Filter by Department and delete rows not applicable
'Step 3 - Loop until the end of the list
Dim Splitcode As Range
Sheets("Master").Select
Set Splitcode = Range("Splitcode")

For Each cell In Splitcode
Sheets("Master").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = cell.Value

With ActiveWorkbook.Sheets(cell.Value).Range("MasterData")
.AutoFilter Field:=4, Criteria1:="<>" & cell.Value, Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

ActiveSheet.AutoFilter.ShowAllData
Next cell
End Sub

我一直收到“该名称已被占用。尝试使用不同的名称。”在线错误(ActiveSheet.Name = cell.Value)。它似乎只是完整地复制了“母版”工作表，而不是通过其余的过滤/复制过程，因为每次我点击运行时它都会生成一个母版 (2) 和母版 (3) 工作表。

Answer 1

您的错误消息是因为代码试图使用已经存在的名称重命名工作表。您的代码几乎是一种“运行一次”的方法。

您可以先删除任何现有工作表来处理错误。

Sub SplitandFilterSheet()

    Dim Splitcode As Range, wb As Workbook, cell As Range, nm As String
    Dim wsMaster As Worksheet

    Set wb = ActiveWorkbook
    Set wsMaster = wb.Sheets("Master")
    Set Splitcode = wsMaster.Range("Splitcode")
    
    For Each cell In Splitcode.Cells
        nm = cell.Value

        On Error Resume Next   'ignore error if no sheet with this name
        wb.Sheets(nm).Delete   'delete any existing sheet with this name
        On Error Goto 0        'stop ignoring errors

        wsMaster.Copy After:=wb.Worksheets(wb.Sheets.Count)
        With wb.Worksheets(wb.Sheets.Count)
            .Name = nm
            With .Range("MasterData")
                .AutoFilter Field:=4, Criteria1:="<>" & nm, Operator:=xlFilterValues
                .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End With
            .AutoFilter.ShowAllData
        End with
    Next cell
End Sub