请求开发VBA模块：Sheet2.A1输入指定值时复制Sheet1对应行至Sheet2

Got it, let's walk through building this VBA automation step by step. This will trigger automatically when you type "2" into Sheet2's A1 cell, copying the matching row from Sheet1's table over to Sheet2.

Step 1: Open the VBA Editor #

• Press Alt + F11 to launch Excel's Visual Basic Editor (VBE).

Step 2: Access Sheet2's Code Module #

• In the left-side Project Explorer, expand your workbook's folder, right-click Sheet2, and select View Code. This ensures our code runs only when changes happen on Sheet2.

Step 3: Paste the VBA Code #

Here's the full code you need—just paste it into the code window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only react if the changed cell is Sheet2.A1
    If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
        ' Check if the entered value is exactly 2
        If Target.Value = 2 Then
            Dim sourceSheet As Worksheet
            Dim destSheet As Worksheet
            Dim matchingRow As Range
            
            ' Set references to our source (Sheet1) and destination (Sheet2) sheets
            Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
            Set destSheet = Me ' "Me" refers to Sheet2 here
            
            ' Find the row in Sheet1's table where Column1 equals 2
            On Error Resume Next
            Set matchingRow = sourceSheet.ListObjects(1).ListColumns(1).DataBodyRange.Find( _
                What:=2, LookIn:=xlValues, LookAt:=xlWhole).EntireRow
            On Error GoTo 0
            
            ' If we found the row, copy it to the next empty row in Sheet2
            If Not matchingRow Is Nothing Then
                ' Paste starting at the first empty row below existing data in Sheet2
                matchingRow.Copy Destination:=destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Offset(1, 0)
                ' Optional: Clear the copy selection border
                Application.CutCopyMode = False
            Else
                ' Alert if no matching row is found
                MsgBox "No row with value 2 found in Sheet1's table!", vbExclamation
            End If
        End If
    End If
End Sub

How This Code Works #

Let's break down the key parts so you understand what's happening:

• The Worksheet_Change event fires every time a cell on Sheet2 is edited. We first check if the edited cell is exactly A1 using Intersect.
• When A1 is set to 2, we grab references to Sheet1 (your table-containing sheet) and Sheet2.
• We use Find to locate the row in Sheet1's table where the first column has a value of 2. Since Sheet1 is formatted as a table, we use ListObjects(1) to access it (if your table has a custom name, replace this with ListObjects("YourTableName")).
• If the row is found, we copy it to the first empty row in Sheet2 (using End(xlUp).Offset(1,0) to jump to the next available row below existing data).
• If no matching row exists, a message box will pop up to let you know.

Quick Notes #

• Double-check that Sheet1 is indeed an Excel Table (select a cell in Sheet1—if you see the Table Design tab at the top, you're good to go).
• If you want the code to trigger when A1's value changes via a formula (not just manual entry), you'll need to use the Worksheet_Calculate event instead. Just let me know if you need that adjustment!

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