VBA运行时错误1004：跨工作簿复制数据随机失效求助

Hey there! I’ve been where you are—staring at that frustrating Error 1004 wondering why your code works sometimes and breaks others. Let’s figure out the root causes and fix this for good, especially with your 200k-row dataset.

Common Reasons for Error 1004 Here #

• Unreliable references: Using Select/Activate (super common for newbies) makes your code depend on the active sheet/workbook, which can shift unexpectedly.
• Missing or misnamed files/worksheets: If "Weather Dashboard" isn’t open, or you misspell the sheet name (case-sensitive!), VB throws an error.
• Locked/protected target sheet: If "Working" is protected, you can’t paste data into it.
• Memory overload: Copying 200k rows all at once can strain Excel’s memory, leading to random failures.

A Robust, Error-Resistant Code Solution #

This code avoids the pitfalls above, uses clear object references, and handles big datasets efficiently:

Sub CopyWeatherDataByDate()
    Dim mainWB As Workbook
    Dim dashboardWB As Workbook
    Dim mainWS As Worksheet
    Dim targetWS As Worksheet
    Dim userDate As Date
    Dim lastRow As Long
    Dim copyRange As Range
    
    ' Turn off screen updates to speed things up and prevent focus issues
    Application.ScreenUpdating = False
    
    ' Set up error handling to catch issues gracefully
    On Error GoTo ErrorHandler
    
    ' 1. Define your workbooks/worksheets clearly
    Set mainWB = ThisWorkbook ' Assumes this code is stored in your main data file
    ' If "Weather Dashboard" might be closed, replace the line below with Workbooks.Open("C:\Your\File\Path\Weather Dashboard.xlsx")
    Set dashboardWB = Workbooks("Weather Dashboard.xlsx")
    Set mainWS = mainWB.Sheets("Sheet1")
    Set targetWS = dashboardWB.Sheets("Working")
    
    ' 2. Get the user's selected date (adjust this if you're picking from a cell instead)
    On Error Resume Next ' Handle cases where user cancels or enters bad data
    userDate = InputBox("Enter the date to filter (format: YYYY/MM/DD)", "Select Date")
    If Err.Number <> 0 Then
        MsgBox "Invalid date format! Please try again."
        Exit Sub
    End If
    On Error GoTo ErrorHandler
    
    ' 3. Find the last row of data in your main sheet (works even for 200k rows)
    ' Update column "A" to match where your dates are stored
    lastRow = mainWS.Cells(mainWS.Rows.Count, "A").End(xlUp).Row
    
    ' 4. Clear any existing filters to avoid conflicts
    If mainWS.AutoFilterMode Then mainWS.AutoFilterMode = False
    
    ' 5. Apply filter to your date column (update Field:=1 to your date column number)
    mainWS.Range("A1:A" & lastRow).AutoFilter Field:=1, Criteria1:=userDate
    
    ' 6. Grab only the visible rows after filtering (excludes hidden rows from other filters)
    ' Update "A1:Z" to match your data's full column range
    Set copyRange = mainWS.Range("A1:Z" & lastRow).SpecialCells(xlCellTypeVisible)
    
    ' 7. Clear old data in target sheet (optional—remove if you want to append instead)
    targetWS.Cells.Clear
    
    ' 8. Paste the filtered data (no Select/Activate needed!)
    copyRange.Copy Destination:=targetWS.Range("A1")
    
    ' 9. Clean up filters
    mainWS.AutoFilterMode = False
    
    MsgBox "Data copied successfully!"
    
Cleanup:
    ' Restore screen updates
    Application.ScreenUpdating = True
    Exit Sub
    
ErrorHandler:
    MsgBox "Oops! Error #" & Err.Number & vbCrLf & "Details: " & Err.Description
    Resume Cleanup
End Sub

Key Improvements to Avoid Error 1004 #

• No Select/Activate: This eliminates the biggest cause of random 1004 errors—your code doesn’t care which sheet is active.
• Explicit object references: Every workbook/worksheet is assigned to a variable, so VB never guesses what you mean.
• Error handling: Catches issues like missing files, bad date inputs, or locked sheets.
• Efficient data handling: Only copies visible filtered rows instead of the entire 200k-row dataset, reducing memory load.

Extra Tips for Your Scenario #

• If "Weather Dashboard" might be closed, replace the Set dashboardWB line with:

  Set dashboardWB = Workbooks.Open("C:\Full\Path\To\Weather Dashboard.xlsx")
  

• If the "Working" sheet is protected, add these lines before/after pasting:

  targetWS.Unprotect Password:="YourPasswordHere" ' Before pasting
  ' ... paste code ...
  targetWS.Protect Password:="YourPasswordHere" ' After pasting
  

• Double-check that your date column in Sheet1 uses the same date format as what the user inputs (or use CDate() to convert input to a date object).

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