修改VBA代码：将整行复制改为仅复制A至Z列的指定行

Great to hear your core logic is working—let's tweak it to copy only columns A through Z instead of the full row, plus fix a common pitfall with row deletion. Here's the revised code with clear explanations:

Sub CopyToChangeLog()
    Dim xRg As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    
    ' Get total used rows in Demand Log
    I = Worksheets("Demand Log").UsedRange.Rows.Count
    ' Get last used row in Change Log (using column B as reference)
    J = Worksheets("Change Log").Cells(Worksheets("Change Log").Rows.Count, "B").End(xlUp).Row
    
    ' Handle empty Change Log sheet edge case
    If J = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("Change Log").UsedRange) = 0 Then J = 0
    End If
    
    ' Define the range to monitor in column O (starting at row 5)
    Set xRg = Worksheets("Demand Log").Range("O5:O" & I)
    
    On Error Resume Next
    Application.ScreenUpdating = False
    
    ' Loop backwards to avoid skipping rows after deletion
    For K = xRg.Count To 1 Step -1
        If CStr(xRg(K).Value) = "Change Team" Then
            J = J + 1
            ' Copy only columns A-Z from the matching row
            Worksheets("Demand Log").Range("A" & xRg(K).Row & ":Z" & xRg(K).Row).Copy _
                Destination:=Worksheets("Change Log").Range("A" & J)
            ' Delete the original row from Demand Log
            xRg(K).EntireRow.Delete
        End If
    Next K
    
    Application.ScreenUpdating = True
End Sub

Key Changes & Improvements: #

• Target Specific Columns: Replaced xRg(K).EntireRow.Copy with a range reference that explicitly targets columns A-Z of the matching row. This ensures only the data you need gets copied.
• Backwards Loop: Switched the loop direction from forward to backward. When you delete a row mid-loop, subsequent rows shift up—forward loops would skip the next row entirely, but looping backwards avoids this issue.
• Minor Cleanup: Added a clear subroutine name for readability, and fixed the CountA check to reference UsedRange (your original code had an incomplete Range reference here).

This version maintains your original workflow while addressing the column-specific copy request and a common bug with row deletion loops.

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