VBA表单内容粘贴至表格下一行定位异常：如何确保精准插入到下一个可用行？

The Problem #

Your current code works sometimes, but fails randomly because it’s relying on the active worksheet for critical calculations (like Rows.Count) instead of explicitly targeting the "CHANGE LOG" sheet. When you run the macro, if a different sheet is active, Rows.Count will use that sheet’s row count instead of the log sheet’s—leading to incorrect paste positions, like pasting to the bottom of the wrong sheet or a random row.

The Fix #

Here’s a revised version of your code that eliminates ambiguity and ensures precise pasting to the next available row in your log table:

Sub LOG_CHG()
    ' Define worksheet variables to avoid relying on active sheet
    Dim wsInput As Worksheet
    Dim wsLog As Worksheet
    Dim lastLogRow As Long
    
    ' Set references to your specific worksheets
    Set wsInput = ThisWorkbook.Sheets("ENTER CHG")
    Set wsLog = ThisWorkbook.Sheets("CHANGE LOG")
    
    ' Find the last used row in column A of the CHANGE LOG sheet
    lastLogRow = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row
    
    ' Copy values directly (more efficient and reliable than copy/paste)
    wsLog.Cells(lastLogRow + 1, "A").Resize(1, 8).Value = wsInput.Range("B8:I8").Value
    
    ' Clear input fields (explicitly target the input sheet to avoid mistakes)
    wsInput.Range("C8:I8").ClearContents
    wsInput.Range("C8").Select
    
    ' Confirmation message for the user
    MsgBox "Your adjustment has been logged.", vbInformation
    
    ' Clean up object references
    Set wsInput = Nothing
    Set wsLog = Nothing
End Sub

Key Improvements #

• Explicit Worksheet References: By defining wsInput and wsLog, we never depend on which sheet is active. This eliminates the random positioning errors caused by using Rows.Count without a sheet context.
• Accurate Last Row Calculation: wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row ensures we’re always looking at the last used row in the log sheet’s column A, not the active sheet.
• Direct Value Assignment: Instead of copy/paste, we assign values directly. This is faster, doesn’t use the clipboard, and avoids issues with cut/copy mode interfering with other actions.
• Clearer, Maintainable Code: Variables make the code easier to read and update later—for example, if you ever need to change sheet names or range sizes.

Edge Case Handling #

If your "CHANGE LOG" sheet is completely empty (no header row), the code will paste starting at row 2. If you want to start at row 1 in that scenario, add this quick check right after calculating lastLogRow:

' Adjust lastLogRow if the log sheet is empty (no header or data)
If lastLogRow = 1 And wsLog.Cells(1, "A").Value = "" Then
    lastLogRow = 0
End If

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