Excel VBA复制粘贴报错：Worksheet Object Failed问题排查

Hey there! Let's walk through why your code is hitting that error and get it working properly. The main issues come from incorrect range references and incomplete syntax—easy fixes once you spot them.

Key Problems in Your Original Code #

• Invalid destination range: oqs.Range("G") isn't a valid Excel range reference. You can't refer to an entire column with just "G"; you need to use Columns("G") or Range("G:G"), but more importantly, we need to target the next empty row in column G, not the whole column.
• Incomplete End() method: Your code cuts off at oqs.Range("G").End(...—you need to specify either xlUp or xlDown here, and xlUp is the reliable choice for finding the last filled row (avoids issues if there are blank cells mid-column).
• Source range limitations: Using H2:H200 might include empty cells, while Range("H2").End(xlDown) stops at the first blank cell in column H. Neither is ideal for grabbing all your actual data.

Corrected Full Code #

Here's a revised version that addresses all these issues, with comments explaining each step:

Sub Mergescript()
    Dim ssaw_p As Worksheet
    Dim oqs As Worksheet
    Dim sourceRange As Range
    Dim destLastRow As Long
    
    ' Set references to your worksheets (ensure sheet names match exactly!)
    Set ssaw_p = ThisWorkbook.Sheets("SSAW_EXPORT")
    Set oqs = ThisWorkbook.Sheets("SQL_IMPORT")
    
    ' Define source range: from H2 to the last non-empty cell in column H
    With ssaw_p
        Set sourceRange = .Range("H2", .Cells(.Rows.Count, "H").End(xlUp))
    End With
    
    ' Find the last filled row in column G of the destination sheet
    destLastRow = oqs.Cells(oqs.Rows.Count, "G").End(xlUp).Row
    
    ' Handle edge case where column G is completely empty
    If destLastRow = 1 And oqs.Range("G1").Value = "" Then
        destLastRow = 0
    End If
    
    ' Copy source data to the next empty row in column G
    sourceRange.Copy Destination:=oqs.Cells(destLastRow + 1, "G")
End Sub

What This Fixes #

1. Reliable source data capture: By starting from the bottom of column H and moving up with End(xlUp), we grab every non-empty cell from H2 down, no matter how many rows your data actually uses.
2. Accurate target row calculation: We find the last filled row in column G, then add 1 to get the next empty row. The edge case check ensures we start at row 1 if G is completely empty.
3. Clear worksheet references: Using ThisWorkbook.Sheets ensures we're referencing sheets in the workbook containing the code, not another open workbook (a common source of "object failed" errors).

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