如何无需按钮实现单元格值控制工作表自动增删？（已实现添加）

Got it, let's fix this for you! Your current code only handles adding new sheets, but doesn't clean up excess ones when the number in cell C1 drops—and it requires manual execution. Here's a fully automated solution that runs without any buttons whenever you change the value in C1:

Step-by-Step Solution #

We'll use the Worksheet_Change event, which triggers automatically when a cell's value changes. This event will handle both deleting extra sheets and adding missing ones based on the number in C1.

Full VBA Code #

Right-click the tab of the worksheet that contains cell C1, select View Code, and paste this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only react to changes in cell C1
    If Not Intersect(Target, Me.Range("C1")) Is Nothing Then
        Dim ws As Worksheet
        Dim numSheetsNeeded As Integer
        Dim currentValidDistroCount As Integer
        Dim sheetNum As Integer
        
        ' Disable screen updates and alerts to avoid flicker/confirmation prompts
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        ' Get the target number of sheets from C1 (handle non-integer values gracefully)
        numSheetsNeeded = IIf(IsNumeric(Me.Range("C1").Value), Me.Range("C1").Value, 0)
        numSheetsNeeded = IIf(numSheetsNeeded < 0, 0, numSheetsNeeded)
        currentValidDistroCount = 0
        
        ' First: Delete any Distro sheets with numbers exceeding the target count
        For Each ws In ThisWorkbook.Sheets
            ' Check if the sheet is a Distro sheet (starts with "Distro-")
            If Left(ws.Name, 7) = "Distro-" Then
                sheetNum = Val(Mid(ws.Name, 8)) ' Extract the number from the sheet name
                
                If sheetNum > numSheetsNeeded Then
                    ws.Delete ' Remove excess sheets
                Else
                    currentValidDistroCount = currentValidDistroCount + 1 ' Count valid existing sheets
                End If
            End If
        Next ws
        
        ' Second: Add missing Distro sheets if needed
        If numSheetsNeeded > currentValidDistroCount Then
            Dim originalSheet As Worksheet
            Set originalSheet = Me ' Reference the sheet with C1 (avoids relying on ActiveSheet)
            
            For i = currentValidDistroCount + 1 To numSheetsNeeded
                ' Copy the original sheet to the end of the workbook
                originalSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                ' Rename the new sheet to match the sequence
                ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = "Distro-" & i
            Next i
        End If
        
        ' Re-enable screen updates and alerts
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    End If
End Sub

Key Improvements Explained #

• Automated Trigger: Runs instantly when you edit cell C1, no buttons required.
• Clean Up First: Deletes any Distro- sheets with numbers higher than the target value before adding new ones.
• Error Resilience: Handles non-numeric or negative values in C1 by defaulting to 0 (which deletes all Distro sheets).
• Reliable References: Uses Me to target the sheet with C1, instead of relying on ActiveSheet (which can cause bugs if you switch sheets mid-operation).
• Smooth Execution: Disables screen updates and alerts to prevent flicker and annoying confirmation popups when deleting sheets.

Notes to Test Safely #

1. Backup Your Workbook: Before testing, save a copy of your file to avoid accidental data loss.
2. Sheet Naming: Ensure no other sheets start with Distro-—the code will treat them as part of the sequence and may delete them if their number exceeds the target.
3. Integer Values: For best results, enter whole numbers in C1 (e.g., 3, 5, 0).

内容的提问来源于stack exchange，提问作者H. Greenland