我正在为工作开发这段代码，在复制数据的那一行就会报错。我大概漏掉了一些就在我眼前的东西。

它在这个语句处专门出错

wsThaw.Range(destRange).Value = wsMenu.Range("E5:E15").Value

我并不是专家，更像是个抄来抄去的人，对用于复制数据的命令中使用的各种变量不熟悉。

谢谢，

Jeremy

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 Range
    Dim dayName As String

    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"
            Set destRange = Range("C5:C15")

        Case "Saturday"
            Set destRange = Range("C20:C30")

        Case "Sunday"
            Set destRange = Range("C35:C45")

        Case "Monday"
            Set destRange = Range("C50:C60")

        Case "Tuesday"
            Set destRange = Range("C65:C75")

        Case "Wednesday"
            Set destRange = Range("C80:C90")

        Case "Thursday"
            Set destRange = Range("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

解决方案

Set destRange = Range("C5:C15")

这行没有合格的工作簿和工作表，因此它默认为活动工作表。

wsPrep.Range(destRange).Value = wsMenu.Range("G5:G15").Value

这行的意思是在 wsprep 上要包含一个在活动工作表上的范围。它无法理解这一点。

使用

wsPrep.Range(destRange.address).Value

你也可以在设置值时对它进行限定，但这样需要在 wsprep 和 wsthaw 两处都做一次

    Set destRange1 = wsprep.Range("C5:C15")
    Set destRange2 = wsthaw.Range("C5:C15")

或者把 Dim destRange As Range 改成 Dim destRange As String 并执行 destRange = "C5:C15"，移除 Set，因为它不再是一个对象，然后你就可以按原样保留你的复制行。