基于日期条件的Excel跨工作簿复制VBA报错求助（运行时错误9）

Hey Stephen, let's break down that frustrating error you're hitting after adding the date filter to your auto-copy VBA. Runtime Error 9 almost always means your code is trying to access an object (workbook, worksheet, range) that doesn't exist, isn't referenced correctly, or has changed since your original code worked. Here's how to diagnose and fix it step by step:

1. Validate Your Source/Target Workbook/Worksheet References #

This is the most common culprit. When you added the filter logic, you might have accidentally broken how your code points to the source (email-received) file or your target workbook.

• Check if the source workbook is properly referenced: If the email file has a dynamic name (e.g., with a date suffix like Data_20240520.xlsx), hardcoding Workbooks("Data.xlsx") will fail. Use a more robust check:

  Dim srcWB As Workbook
  On Error Resume Next
  ' Replace with your actual source file name pattern or use GetObject if it's not open
  Set srcWB = Workbooks("YourReceivedDataFileName.xlsx")
  On Error GoTo 0
  
  If srcWB Is Nothing Then
      MsgBox "Source workbook not found! Please ensure it's open."
      Exit Sub
  End If
  

• Verify worksheet names haven't changed: Double-check that your source/target sheet names match what's in your code. For example, if the source sheet was renamed from RawData to DailyImport, srcWB.Sheets("RawData") will throw an error. Add a similar check for worksheets:

  Dim srcWS As Worksheet
  On Error Resume Next
  Set srcWS = srcWB.Sheets("YourSourceSheetName")
  On Error GoTo 0
  
  If srcWS Is Nothing Then
      MsgBox "Source worksheet missing! Check the sheet name in your source file."
      Exit Sub
  End If
  

2. Fix Date Type Mismatches & Filter Range Issues #

Your new logic filters column P to match AV1's date—but if the data types don't align, the filter might return no rows, and subsequent copy attempts on an empty range can trigger the error.

• Ensure consistent date formatting: AV1 might be a true date value, but column P could be stored as text. Convert both to the same type before filtering:

  Dim targetDate As Date
  ' Explicitly reference your target workbook/sheet to avoid confusion
  targetDate = ThisWorkbook.Sheets("YourTargetSheet").Range("AV1").Value
  
  ' Define your source data range (avoid using entire columns like P:P)
  Dim srcDataRange As Range
  Set srcDataRange = srcWS.Range("A1").CurrentRegion ' Assumes data starts at A1
  
  ' Apply filter with date conversion
  srcDataRange.AutoFilter Field:=16, Criteria1:=CDate(targetDate) ' 16 = column P
  
  ' Check if any rows were returned (skip header)
  Dim filteredRows As Range
  On Error Resume Next
  Set filteredRows = srcDataRange.Offset(1).SpecialCells(xlCellTypeVisible)
  On Error GoTo 0
  
  If filteredRows Is Nothing Then
      MsgBox "No rows match the target date in column P!"
      srcDataRange.AutoFilter ' Clear filter
      Exit Sub
  End If
  
  ' Copy the valid filtered rows
  filteredRows.Copy Destination:=ThisWorkbook.Sheets("YourTargetSheet").Range("YourStartCell")
  srcDataRange.AutoFilter ' Clean up filter
  

3. Avoid Ambiguous Workbook Contexts #

If your code uses ActiveWorkbook instead of explicit references, switching between the target workbook and source file can break object access. Always use ThisWorkbook for the workbook containing your VBA code, and store external workbooks in variables (like srcWB above) instead of relying on active state.

4. Debug to Pinpoint the Exact Error Line #

The fastest way to narrow down the problem is to use breakpoints:

1. Open the VBA editor (Alt+F11)
2. Click the gray margin next to each line of code to set a breakpoint (red dot)
3. Run your code (F5) and step through line by line (F8)
4. Note which line triggers the error—this will tell you exactly which object reference is broken.

Start with these steps, and you'll likely find the root cause quickly. The key is verifying every object your code touches (workbooks, sheets, ranges) exists and is referenced correctly.

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