如何实现Excel中匹配状态值的单元格自动复制至对应工作表？——门禁卡状态追踪自动化需求咨询

Hey Mike, I've got two practical solutions to set up your access card tracking system exactly how you need it—one using VBA for automatic historical logging, and another using dynamic formulas for real-time status sync. Let's break them down:

Option 1: VBA Macro for Automatic Status Logging (Best for Historical Tracking) #

This solution will automatically copy the relevant row to the corresponding status worksheet every time you update the Card Status in the Main sheet—perfect if you want to keep a history of all status changes over time.

Step-by-Step Setup: #

1. Open your Excel file, press Alt + F11 to launch the VBA Editor.
2. In the Project Explorer pane, locate your workbook, then double-click the Main worksheet to open its code window.
3. Paste the following code into the window:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Define the column index for Card Status (adjust if your column is different)
    Const STATUS_COLUMN As Integer = 1
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim statusValue As String
    
    ' Only trigger if the changed cell is in the Card Status column (single cell edit)
    If Target.Column = STATUS_COLUMN And Target.Cells.Count = 1 Then
        ' Clean up status text to match worksheet names (remove "Card " prefix)
        statusValue = Replace(Target.Value, "Card ", "")
        
        ' Check if the target worksheet exists
        On Error Resume Next
        Set targetSheet = ThisWorkbook.Worksheets(statusValue)
        On Error GoTo 0
        
        If Not targetSheet Is Nothing Then
            ' Find the next empty row in the target worksheet
            lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1
            
            ' Copy full row data from Main to target sheet (values only to avoid formatting issues)
            Target.EntireRow.Copy
            targetSheet.Cells(lastRow, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            
            ' Clear clipboard to prevent lingering copy data
            Application.CutCopyMode = False
        Else
            ' Show warning if status doesn't match any worksheet name
            MsgBox "Warning: Worksheet for '" & Target.Value & "' doesn't exist!", vbExclamation
        End If
    End If
End Sub

4. Save your file as a Macro-Enabled Workbook (.xlsm) to preserve the VBA code.

How It Works: #

• When you edit any cell in the Card Status column (column A in your example), the macro triggers immediately.
• It strips the "Card " prefix from the status text to match your worksheet names (Issued, Faulty, etc.).
• It finds the corresponding worksheet and pastes the full row of data (status, employee, card number, date) to the next empty row.
• If you enter a status that doesn't have a matching worksheet, it shows a clear warning to avoid missing data.

Option 2: Dynamic Array Formulas (Best for Real-Time Sync, No Macros) #

If you prefer avoiding macros (e.g., for security or compatibility reasons), this solution uses Excel's dynamic array functions (available in Excel 365/2021) to automatically sync rows from Main to each status worksheet in real time.

Setup for Each Status Worksheet: #

1. Open the Issued worksheet, click cell A2, then enter this formula:

=FILTER(Main!A:D, Main!A:A="Card Issued", "No cards in this status")

2. Repeat this for the other status worksheets, updating the status text in the formula:
   • Faulty: =FILTER(Main!A:D, Main!A:A="Card Faulty", "No cards in this status")
   • Lost: =FILTER(Main!A:D, Main!A:A="Card Lost", "No cards in this status")
   • Returned: =FILTER(Main!A:D, Main!A:A="Card Returned", "No cards in this status")

How It Works: #

• The FILTER function automatically pulls all rows from the Main sheet where the Card Status matches the worksheet's purpose.
• When you update a card's status in Main, the corresponding status worksheet instantly refreshes to show or remove the row.
• If there are no cards in that status, it displays a custom message to avoid empty tables.

Key Note: #

• This solution shows current status only (not historical changes). If you need to track how a card's status has changed over time, stick with the VBA option.

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