VBA复制行至另一工作表时第10行后固定停在第1行求助

问题背景 #

需求：将Chemicals工作表中双击选中行的D:M区域内容，复制到Bill of Lading工作表的BILLLAD表格（数据区域为A10至J27）。
问题：代码在复制前10行（对应工作表行10-19）时正常，之后会固定停在目标区域第1行重复粘贴。已尝试将目标表格转为普通区域，无效，且未发现其他干扰代码。

原代码 #

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim thisRow As Long
    Dim nwSh As Worksheet
    Dim newRow As Long

    Set nwSh = ThisWorkbook.Sheets("Bill of Lading")
    newRow = nwSh.UsedRange.Rows(nwSh.Range("a9:j27").Rows.Count).End(xlUp).Offset(1).Row
    thisRow = ActiveCell.Row
    Intersect(ActiveCell.EntireRow, Range("d:m")).Copy Sheets("Bill of Lading").Range("a" & newRow)

    'MsgBox nwSh.UsedRange.Rows(nwSh.Range("a9:j27").Rows.Count).End(xlUp).Offset(1).Row
End Sub

问题排查 #

核心错误在于newRow的计算逻辑：
nwSh.UsedRange.Rows(nwSh.Range("a9:j27").Rows.Count).End(xlUp).Offset(1).Row 这行代码逻辑混乱：

1. nwSh.Range("a9:j27").Rows.Count得到的是19（27-9+1），仅表示目标区域的总行数
2. nwSh.UsedRange.Rows(19)是取已用区域的第19行，而非目标区域的最后一行
3. 当目标区域前10行填满后，End(xlUp)会跳转到已用区域的顶部非空行，导致newRow始终返回同一个值，重复粘贴到同一位置

修正后的代码 #

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim thisRow As Long
    Dim nwSh As Worksheet
    Dim lastRowInTarget As Long
    Dim newRow As Long
    
    Set nwSh = ThisWorkbook.Sheets("Bill of Lading")
    
    ' 在目标区域A10:A27内反向查找最后一个非空单元格的行号
    lastRowInTarget = nwSh.Range("A10:A27").Find(What:="*", _
                        LookIn:=xlValues, _
                        SearchDirection:=xlPrevious).Row
    
    ' 判断目标区域是否还有剩余空行
    If lastRowInTarget < 27 Then
        newRow = lastRowInTarget + 1
        thisRow = Target.Row ' 使用Target而非ActiveCell，确保是双击触发的行
        ' 复制当前行D:M区域到目标行A列起始位置
        Intersect(Target.EntireRow, Me.Range("D:M")).Copy nwSh.Range("A" & newRow)
        Cancel = True ' 取消双击默认的单元格编辑模式
    Else
        MsgBox "目标区域已填满，无法继续粘贴！"
    End If
End Sub

关键修正点 #

• 替换ActiveCell为Target：Target是双击事件的触发源，比ActiveCell更可靠，避免其他操作干扰行定位
• 精准定位目标区域最后一行：通过Find方法在指定目标范围（A10:A27）内反向查找，确保获取的是目标区域内的最后一个非空行
• 添加边界校验：当目标区域（A27）已填满时，弹出提示，防止越界粘贴
• 取消双击编辑模式：加入Cancel=True，避免双击后进入单元格编辑状态，提升操作体验

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