基于星期几复制数据的VBA脚本报错求助

错误原因 #

你这段代码的核心问题出在destRange的定义和使用上：

• 你把destRange声明为Range对象，但赋值时没指定所属工作表，默认绑定到当前活动工作表。之后执行wsThaw.Range(destRange)时，VBA会试图在「Daily thaw」工作表里引用属于其他工作表的Range对象，这就触发了报错。
• 另外代码里的selectedSheet变量没有提前声明，属于隐式声明，虽然不一定直接报错，但容易引发其他潜在问题。

修复后的完整代码 #

Option Explicit ' 强制变量声明，避免隐式声明问题

Sub Test()

' Copy information Visual Basic control _
  Script written on 05/07/2026 by me _
  Copies data to appropriate locations for Thaw and Prep worksheets

    Dim wsMenu As Worksheet
    Dim wsThaw As Worksheet
    Dim wsPrep As Worksheet
    Dim destRange As String ' 改为字符串类型，存储单元格地址
    Dim dayName As String
    Dim selectedSheet As Worksheet ' 补全变量声明

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    ' Set worksheet references
    Set selectedSheet = ActiveSheet
    Set wsMenu = ThisWorkbook.Worksheets("Menu")
    Set wsThaw = ThisWorkbook.Worksheets("Daily thaw")
    Set wsPrep = ThisWorkbook.Worksheets("Daily prep")

    dayName = Format(Date, "dddd")

    ' Determine destination by day of week
    Select Case dayName
    
        Case "Friday"
            destRange = "C5:C15" ' 直接赋值地址字符串
            
        Case "Saturday"
            destRange = "C20:C30"
            
        Case "Sunday"
            destRange = "C35:C45"
            
        Case "Monday"
            destRange = "C50:C60"
            
        Case "Tuesday"
            destRange = "C65:C75"
            
        Case "Wednesday"
            destRange = "C80:C90"
            
        Case "Thursday"
            destRange = "C95:C105"
            
        Case Else
            GoTo CleanExit
            
    End Select

    ' Copy Menu to Daily Thaw
    wsThaw.Range(destRange).Value = wsMenu.Range("E5:E15").Value

    ' Copy Menu to Daily Prep
    wsPrep.Range(destRange).Value = wsMenu.Range("G5:G15").Value

    ' Clear source ranges
    wsMenu.Range("E5:E15, G5:G15").ClearContents

CleanExit:

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
        
        
End Sub

关键修改说明 #

1. 添加Option Explicit：放在代码最顶部，强制所有变量必须提前声明，避免因拼写错误或隐式声明导致的奇怪问题。
2. 修改destRange类型：从Range改为String，直接存储目标单元格的地址字符串，这样后续在wsThaw和wsPrep中调用Range(destRange)时，能正确在对应工作表中找到目标区域。
3. 补全selectedSheet声明：明确声明变量类型，符合VBA编码规范。

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