VBA跨文件复制非空首行数据报错及目标文件验证问题求助

Hey there, fellow VBA newbie! Let's tackle your problem head-on—you're not alone with these tricky little bugs. Let's break down what's going wrong and fix it step by step.

First, let's diagnose the two main issues: #

1. Macro can't open the target file on subsequent runs: This almost always happens because the target workbook wasn't properly closed and released from memory. Excel keeps a lock on the file in the background, so your macro can't access it again.
2. Data validation error when opening manually: Your copy-paste operation is probably corrupting the data validation rules in Sheet2 of the target file. Full row copies can sometimes overwrite or break existing cell formatting/validation.

Fix 1: Properly close and release workbook objects #

This is critical—never skip releasing your workbook objects! Here's how to do it right:

Dim targetWB As Workbook

' Open the target file
Set targetWB = Workbooks.Open("C:\Your\Target\File\Path.xlsx")

' Do your copy operations here...

' Close and save, then release the object
targetWB.Close SaveChanges:=True
Set targetWB = Nothing ' This frees up memory and removes the file lock

If you don't run Set targetWB = Nothing, Excel might leave a hidden process running that holds onto the file, making it unopenable for your next macro run.

Fix 2: Avoid corrupting data validation with safer pasting #

Instead of copying entire rows (which can carry over unintended formatting or break existing validation), use a targeted paste method. For example, paste values and number formats without overwriting cell rules:

' Copy from source row to target row
sourceSheet.Rows(currentRow).Copy
targetSheet.Rows(nextTargetRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False ' Clear the clipboard to avoid issues

If you need to keep formatting, you can use xlPasteAllExceptBorders or xlPasteFormats instead—pick the option that matches your needs without messing up the target sheet's existing setup.

Full working example code #

Here's a complete, beginner-friendly script that handles everything correctly:

Sub CopyNonEmptyRows()
    Dim sourceWB As Workbook
    Dim sourceSheet As Worksheet
    Dim targetWB As Workbook
    Dim targetSheet As Worksheet
    Dim lastSourceRow As Long
    Dim currentRow As Long
    Dim nextTargetRow As Long
    
    ' Set up your source workbook/sheet (uses the file with the macro)
    Set sourceWB = ThisWorkbook
    Set sourceSheet = sourceWB.Sheets("Sheet1") ' Replace with your source sheet name
    
    ' Find the last row with data in the source sheet
    lastSourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row
    
    ' Try to open the target file (add error handling)
    On Error Resume Next
    Set targetWB = Workbooks.Open("C:\Path\To\Your\TargetFile.xlsx") ' Replace with your file path
    On Error GoTo 0
    
    ' If the file couldn't open, show a message and exit
    If targetWB Is Nothing Then
        MsgBox "Oops! Couldn't open the target file. Double-check the file path."
        Exit Sub
    End If
    
    ' Set up your target sheet
    Set targetSheet = targetWB.Sheets("Sheet2") ' Replace with your target sheet name
    ' Find the next empty row in the target sheet
    nextTargetRow = targetSheet.Cells(targetSheet.Rows.Count, 1).End(xlUp).Row + 1
    
    ' Loop through each row in the source sheet
    For currentRow = 1 To lastSourceRow
        ' Check if the first cell of the row isn't empty
        If sourceSheet.Cells(currentRow, 1).Value <> "" Then
            ' Copy and paste safely
            sourceSheet.Rows(currentRow).Copy
            targetSheet.Rows(nextTargetRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            Application.CutCopyMode = False
            ' Move to the next empty row in the target sheet
            nextTargetRow = nextTargetRow + 1
        End If
    Next currentRow
    
    ' Clean up: close target file and release all objects
    targetWB.Close SaveChanges:=True
    Set targetWB = Nothing
    Set targetSheet = Nothing
    Set sourceSheet = Nothing
    Set sourceWB = Nothing
    
    MsgBox "Done! All non-empty rows have been copied."
End Sub

Extra troubleshooting tips #

• Check for file locks: Make sure no other program (like another Excel window, or even antivirus) is using the target file before running the macro.
• Fix the data validation error manually: Open the target file, go to Sheet2, select the cells that had validation, and either re-set the validation rules or clear them if you don't need them. Save the file, then try running the macro again.

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