求助：修改代码实现将含"Geoff"的整行复制到指定Excel工作表

Got it, let's adjust your VBA code so it copies the entire row whenever "Geoff" is found in column C, instead of just the single cell. Here's the modified code, plus a breakdown of the key changes so you understand exactly what's happening:

Modified VBA Code #

Sub CopyGeoffRows()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim targetRow As Long
    
    ' Set reference to the active sheet (your source data)
    Set wsSource = ActiveSheet
    
    ' Check if the "Geoff" sheet exists
    On Error Resume Next
    Set wsTarget = ThisWorkbook.Worksheets("Geoff")
    On Error GoTo 0
    
    If wsTarget Is Nothing Then
        MsgBox "Please create a worksheet named 'Geoff' first!", vbExclamation
        Exit Sub
    End If
    
    ' Find the last row with data in column C of the source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "C").End(xlUp).Row
    
    ' Start pasting at row 1 (change to 2 if your target sheet has headers)
    targetRow = 1
    
    ' Speed up the macro by turning off screen updates
    Application.ScreenUpdating = False
    
    ' Loop through each row in column C
    For i = 1 To lastRow
        ' Check if the cell contains "Geoff" (case-insensitive)
        If InStr(1, wsSource.Cells(i, "C").Value, "Geoff", vbTextCompare) > 0 Then
            ' Copy the entire row from the source sheet
            wsSource.Rows(i).Copy
            
            ' Paste the row to the target sheet's next available row
            wsTarget.Rows(targetRow).PasteSpecial Paste:=xlPasteAll
            
            ' Move to the next row in the target sheet for the next entry
            targetRow = targetRow + 1
        End If
    Next i
    
    ' Clean up and restore settings
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    MsgBox "All matching rows copied to 'Geoff' sheet!", vbInformation
End Sub

Key Changes Explained #

• Copy Entire Row: Instead of copying just the cell in column C (Range("C" & i).Copy), we use wsSource.Rows(i).Copy to grab every cell in the row.
• Paste Entire Row: We paste to wsTarget.Rows(targetRow) using xlPasteAll to preserve all data, formatting, and formulas. If you only need values, replace xlPasteAll with xlPasteValues.
• Target Sheet Check: Added a safety check to ensure the "Geoff" sheet exists—no more runtime errors if the sheet is missing!
• Efficiency: Disabled screen updating during the loop to make the macro run much faster, especially with large datasets.
• Case-Insensitive Search: Used vbTextCompare in InStr so it finds "geoff", "GEOFF", or "Geoffrey" (remove this parameter if you want an exact case match only).

Quick Notes #

• If your source sheet has headers, start the loop at i = 2 instead of 1 to skip the header row.
• If your "Geoff" sheet already has headers, set targetRow = 2 so you don't overwrite them.

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