Excel VBA复制数据报错：Sheet1非空I列行复制至Sheet2时遇错误9/450

Hey there! Let's break down why your copy-paste VBA code is throwing Error 9 (Subscript Out of Range) or Error 450 (Wrong Number of Arguments or Invalid Property Assignment), and how to fix it.

First: Is the Sheet2 Button the Culprit? #

Short answer: Not directly. But it can contribute to the problem if your code doesn't explicitly specify which worksheet you're referencing. When you click a button on Sheet2, Sheet2 becomes the ActiveSheet by default. If your original code uses unqualified references like Cells(i, "I") or Rows(i), it'll try to read from Sheet2 instead of Sheet1—this is almost certainly causing Error 9 (since Sheet2 might not have the data structure you're expecting) or Error 450 (if the copy range is invalid on Sheet2).

Why Your .Copy Line Is Failing #

Error 450 often pops up if your Copy method has incorrect syntax (e.g., missing a destination, or referencing a range that doesn't exist). Error 9 happens when you try to access a worksheet or range that doesn't exist (like accidentally pointing to Sheet2 instead of Sheet1).

A More Reliable Solution: Ditch .Copy for Direct Value Assignment #

Instead of relying on the Copy method (which can be finicky with the clipboard, sheet activation, and syntax), use direct value assignment. It's faster, more stable, and avoids most common copy-paste errors. Here's a revised version of your code:

Option Explicit

Sub copyPositiveNotesData()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim lastRowSource As Long
    Dim nextRowDest As Long
    Dim i As Long
    
    ' Explicitly define your source and destination worksheets
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    
    ' Find the last row with data in Sheet1's column I
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "I").End(xlUp).Row
    
    ' Loop through each row in Sheet1 (skip row 1 if it's a header)
    For i = 2 To lastRowSource
        ' Check if column I is not empty (trim to ignore blank spaces)
        If Trim(wsSource.Cells(i, "I").Value) <> "" Then
            ' Find the next blank row in Sheet2's column A
            nextRowDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1
            
            ' Assign values directly (no copy-paste needed!)
            wsDest.Cells(nextRowDest, "A").Value = wsSource.Cells(i, "A").Value
            wsDest.Cells(nextRowDest, "B").Value = wsSource.Cells(i, "B").Value
            wsDest.Cells(nextRowDest, "I").Value = wsSource.Cells(i, "I").Value
            wsDest.Cells(nextRowDest, "L").Value = wsSource.Cells(i, "L").Value
            
            ' Optional: If you need to copy formatting too, uncomment these lines
            ' wsSource.Cells(i, "A").Copy
            ' wsDest.Cells(nextRowDest, "A").PasteSpecial xlPasteFormats
            ' wsSource.Cells(i, "B").Copy
            ' wsDest.Cells(nextRowDest, "B").PasteSpecial xlPasteFormats
            ' ' Repeat for I and L columns if needed
            ' Application.CutCopyMode = False ' Clear clipboard
        End If
    Next i
    
    MsgBox "Data copied successfully!", vbInformation
End Sub

Key Improvements in This Code #

• Explicit Worksheet References: By defining wsSource and wsDest, we eliminate any ambiguity about which sheet we're working with—no more accidental references to Sheet2 just because the button is there.
• Direct Value Assignment: This avoids the pitfalls of the Copy method (like clipboard conflicts or syntax errors) and runs much faster for large datasets.
• Trimmed Check: Using Trim() ensures we don't count cells with only spaces as "non-empty".

Final Notes #

If you still want to use the Copy method (e.g., to preserve formulas or complex formatting), just make sure you fully qualify your ranges. For example:

wsSource.Range(wsSource.Cells(i, "A"), wsSource.Cells(i, "B")).Copy _
    Destination:=wsDest.Cells(nextRowDest, "A")
wsSource.Cells(i, "I").Copy Destination:=wsDest.Cells(nextRowDest, "I")
wsSource.Cells(i, "L").Copy Destination:=wsDest.Cells(nextRowDest, "L")

But again, direct assignment is the more robust choice for simple value copying.

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