如何将含特定值的指定单元格复制到Excel新工作表？

Got it, let's build a VBA solution that does exactly what you need—scanning a specified range for your target value (1234) and copying the right cells to a new worksheet every time we hit a match.

Here's the full code you can drop into your Excel VBA editor: #

Sub CopyMatchingCells()
    Dim sourceWS As Worksheet
    Dim targetWS As Worksheet
    Dim searchRange As Range
    Dim cell As Range
    Dim targetValue As Variant
    Dim pasteRow As Long
    
    ' Set your source worksheet (uses Worksheet(1) as you specified)
    Set sourceWS = ThisWorkbook.Worksheets(1)
    ' Create a new worksheet for results
    Set targetWS = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    targetWS.Name = "MatchedResults"
    
    ' Define your target value and search range
    targetValue = 1234
    Set searchRange = sourceWS.Range("M1:X155")
    
    ' Initialize the first row to paste in the target sheet
    pasteRow = 1
    
    ' Loop through each cell in the search range
    For Each cell In searchRange
        ' Check if the cell contains the target value
        If cell.Value = targetValue Then
            ' Copy the specified cells from the matching row
            sourceWS.Cells(cell.Row, "A").Copy targetWS.Cells(pasteRow, "A")
            sourceWS.Cells(cell.Row, "B").Copy targetWS.Cells(pasteRow, "B")
            sourceWS.Cells(cell.Row, "C").Copy targetWS.Cells(pasteRow, "C")
            sourceWS.Cells(cell.Row, "F").Copy targetWS.Cells(pasteRow, "D")
            cell.Copy targetWS.Cells(pasteRow, "E") ' Copy the cell with the target value
            
            ' Move to the next row for the next match
            pasteRow = pasteRow + 1
        End If
    Next cell
    
    ' Optional: Auto-fit columns in the results sheet for readability
    targetWS.Columns.AutoFit
    
    MsgBox "Done! Matched data has been copied to the 'MatchedResults' sheet.", vbInformation
End Sub

Let's break down what this code does: #

• Setup: We first link to your source worksheet (using Worksheets(1) as you mentioned) and create a new sheet named "MatchedResults" to store our output.
• Search Parameters: We set the target value to 1234 and define the exact range we want to scan (M1:X155).
• Loop & Match: We go through every cell in the search range. When we find a cell with the target value, we copy the corresponding A, B, C, F cells from that row, plus the matching cell itself, to the next empty row in the results sheet.
• Polish: Finally, we auto-fit the columns in the results sheet and pop up a quick confirmation message so you know it's done.

Quick customization tips: #

• If your source sheet isn't the first one, replace Worksheets(1) with the sheet name in quotes (e.g., Worksheets("SalesData")).
• To change the target value, update the targetValue variable (use quotes like "1234" if you're searching for a text string instead of a number).
• Adjust the search range by modifying Range("M1:X155") to your actual range.
• Swap out column letters (like "F" for "G") if you need to copy different cells from the matching row.

To use this:

1. Open your Excel file.
2. Press Alt + F11 to open the VBA editor.
3. Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste the code above into the module.
5. Press F5 to run the macro, or assign it to a button in your worksheet for easier access.

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