PAYATHON 2026

Excel VBA 按星期复制数据时,目标 Range 赋值报错如何解决?

数据小陈

问题主要出在 destRange 的用法上:它是绑定到某张工作表的 Range 对象,不能当作跨工作表通用的地址。两张目标表需要使用相同的单元格地址,可以用 String 保存地址,再分别通过 wsThaw.Range(destAddress) 和 wsPrep.Range(destAddress) 获取目标区域。

原问题没有提供错误编号和完整提示,因此无法判断是否还有工作表保护等其他问题。代码中的目标区域引用需要调整。

为什么当前写法容易报错?

下面这行代码没有限定工作表:

Set destRange = Range("C5:C15")

在普通标准模块中,这种写法通常引用当前活动工作表上的区域。如果活动表是 Menu,destRange 就指向 Menu!C5:C15,并且带有所属工作表的信息。

接着执行:

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

外层的 wsThaw.Range(...) 不会把已有的 Range 对象自动转换为 Daily thaw 上同一地址的区域。跨工作表传入这个对象会造成引用问题。

两张目标表只需要共享地址,可以这样写:

Dim destAddress As String

destAddress = "C5:C15"

wsThaw.Range(destAddress).Value = wsMenu.Range("E5:E15").Value
wsPrep.Range(destAddress).Value = wsMenu.Range("G5:G15").Value

给字符串赋值时不使用 Set。

完整修改示例

下面的版本保留原来的星期与目标区域对应关系,还做了两处调整:

  • 使用 Weekday(Date, vbFriday) 判断星期,避免 Format(Date, "dddd") 返回本地语言名称后无法匹配英文 Case。
  • 保存 Excel 原有的应用程序设置,并在正常结束或发生可捕获的运行时错误后恢复。
Option Explicit

Sub Test()
    Dim wsMenu As Worksheet
    Dim wsThaw As Worksheet
    Dim wsPrep As Worksheet
    Dim destAddress As String

    Dim previousScreenUpdating As Boolean
    Dim previousEnableEvents As Boolean
    Dim previousDisplayAlerts As Boolean

    Dim errorNumber As Long
    Dim errorDescription As String

    previousScreenUpdating = Application.ScreenUpdating
    previousEnableEvents = Application.EnableEvents
    previousDisplayAlerts = Application.DisplayAlerts

    On Error GoTo HandleError

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False

    Set wsMenu = ThisWorkbook.Worksheets("Menu")
    Set wsThaw = ThisWorkbook.Worksheets("Daily thaw")
    Set wsPrep = ThisWorkbook.Worksheets("Daily prep")

    ' vbFriday makes Friday day 1 and Thursday day 7.
    Select Case Weekday(Date, vbFriday)
        Case 1
            destAddress = "C5:C15"
        Case 2
            destAddress = "C20:C30"
        Case 3
            destAddress = "C35:C45"
        Case 4
            destAddress = "C50:C60"
        Case 5
            destAddress = "C65:C75"
        Case 6
            destAddress = "C80:C90"
        Case 7
            destAddress = "C95:C105"
        Case Else
            GoTo CleanExit
    End Select

    ' Copy values to both destination worksheets.
    wsThaw.Range(destAddress).Value = wsMenu.Range("E5:E15").Value
    wsPrep.Range(destAddress).Value = wsMenu.Range("G5:G15").Value

    ' Clear source cells after both assignments succeed.
    wsMenu.Range("E5:E15,G5:G15").ClearContents

CleanExit:
    Application.ScreenUpdating = previousScreenUpdating
    Application.EnableEvents = previousEnableEvents
    Application.DisplayAlerts = previousDisplayAlerts

    If errorNumber <> 0 Then
        MsgBox "Error " & errorNumber & ": " & errorDescription, _
               vbExclamation, "Test"
    End If

    Exit Sub

HandleError:
    errorNumber = Err.Number
    errorDescription = Err.Description
    Resume CleanExit
End Sub

Weekday(Date, vbFriday) 将星期五作为一周的第一天,返回值 1 到 7 分别对应星期五到星期四,与原代码的目标地址安排一致。

如果继续使用 Range 对象,应该怎样写?

也可以使用 Range 对象,需要明确指定所属工作表,再直接对对象赋值。例如,星期五的复制操作可以写成:

Dim thawDestination As Range
Dim prepDestination As Range

Set thawDestination = wsThaw.Range("C5:C15")
Set prepDestination = wsPrep.Range("C5:C15")

thawDestination.Value = wsMenu.Range("E5:E15").Value
prepDestination.Value = wsMenu.Range("G5:G15").Value

这里两张工作表各有一个目标 Range。当前两张表共用地址,使用 destAddress As String 更方便。

运行前需要确认什么?

  1. ThisWorkbook 指包含这段 VBA 代码的工作簿。示例假定 Menu、Daily thaw 和 Daily prep 都在其中;如果宏存放在其他工作簿,需要调整工作簿引用。
  2. 表名必须与代码一致,工作表和目标单元格也需要可写。如果目标区域受保护或包含不匹配的合并单元格,修改引用后仍可能报错,需要结合错误编号继续排查。
  3. .Value = ... .Value 只复制值,不复制格式;源单元格包含公式时,写入的是公式的计算结果。示例中的源区域与目标区域均为 11 行、1 列,尺寸一致。
  4. ClearContents 会删除源区域中的值和公式,并保留格式。代码在两次写入成功后才清空源数据,但这些操作没有自动回滚:如果第二次写入失败,第一次已经写入的数据仍会保留。

原代码中的 Set selectedSheet = ActiveSheet 后续没有使用,可以删除。启用 Option Explicit 后,未声明的 selectedSheet 也会导致编译错误。

备注:内容仅供参考。