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 更方便。
运行前需要确认什么?
ThisWorkbook指包含这段 VBA 代码的工作簿。示例假定Menu、Daily thaw和Daily prep都在其中;如果宏存放在其他工作簿,需要调整工作簿引用。- 表名必须与代码一致,工作表和目标单元格也需要可写。如果目标区域受保护或包含不匹配的合并单元格,修改引用后仍可能报错,需要结合错误编号继续排查。
.Value = ... .Value只复制值,不复制格式;源单元格包含公式时,写入的是公式的计算结果。示例中的源区域与目标区域均为 11 行、1 列,尺寸一致。ClearContents会删除源区域中的值和公式,并保留格式。代码在两次写入成功后才清空源数据,但这些操作没有自动回滚:如果第二次写入失败,第一次已经写入的数据仍会保留。
原代码中的 Set selectedSheet = ActiveSheet 后续没有使用,可以删除。启用 Option Explicit 后,未声明的 selectedSheet 也会导致编译错误。
备注:内容仅供参考。
