打开第二个工作簿时Excel VBA定时器代码报错原因排查

WB1单独运行时功能正常：其某工作表通过定时器从RTD获取股票期权数据，并定期将数据写入另一工作表。定时器触发时，代码首先选中名为“RTD”的工作表，动态获取该表当前数据的首行、末行及列范围，将数据复制到存储当日RTD数据的工作表中。
但当打开与WB1无关联的第二个工作簿WB2，并手动新建工作表时，WB1中运行的定时器代码出现报错。请求排查该报错的原因及解决方法。

核心代码 #

Sub saveRTDinfo()
    
    'copy a dynamic range to the end of sheet where copied data is kept
    'first get source range of cells to copy
    Dim x, rowA, rowB, colA, colB As String
    'don't need target sheet start column, assume target column is 1
    'don't need target sheet start row, assume target row is 2
    'MsgBox destSheet
    'destSheet = "040624" 'destSheet now set todays date when excel starts
    
    Sheets("RTD").Select
    Sheets("RTD").Activate
    
    'Set wb1RTD = ThisWorkbook.Sheets("RTD")
    'Set wb1dest = ThisWorkbook.Sheets("021225")
    
    On Error Resume Next
    copyRowEnd = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    lastRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Row
    Resume Next
    Dim rng As Range, cell As Range
    
    'Set rng = Range("A1:A10")

    copyColEnd = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious).Column
    On Error GoTo 0
    'MsgBox "Last used Col number: " & copyColEnd
    'build and copy range of current cells (range repeats to get values over time)
    'range always starts r2c1 to allow for column titles
    copyRowStart = 2
    copyColStart = 1
    Range(Cells(copyRowStart, copyColStart), Cells(copyRowEnd, copyColEnd)).Copy
    'ActiveSheet.(Cells(1, 1),CELLS(2,1)).Copy

    'now set up target start cell to copy range, values only
    Sheets(destSheet).Select
    Sheets(destSheet).Activate
    'paste into first unused row, always column 1
    destRowStart = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    destRowStart = destRowStart + 1
    
    destColStart = 1
    ActiveSheet.Cells(destRowStart, destColStart).PasteSpecial Paste:=xlPasteValues
    ' wb1dest.Cells(destRowStart, destColStart).PasteSpecial Paste:=xlPasteValues
    
    'Range(Cells(lastRow, 1), Cells(2, 1)).PasteSpecial Paste:=xlPasteValues
    'PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     ':=False, Transpose:=False

End Sub

• 未绑定工作簿对象：代码里用Sheets("RTD")、Range、Cells时，没指定属于WB1，默认会找当前激活的工作簿。打开WB2并新建工作表时，激活状态可能切到WB2，此时代码会在WB2里找“RTD”工作表，自然找不到报错。
• 错误处理不规范：On Error Resume Next和Resume Next的写法不符合语法，Resume Next要放在错误处理块里，当前写法会掩盖错误，没法定位问题。
• 依赖Select/Activate：这类操作完全靠当前激活的窗口/工作表，一旦激活对象变了，代码逻辑就乱了。

核心是明确绑定WB1的工作簿和工作表对象，彻底删掉Select/Activate操作，同时规范错误处理。修改后的代码如下：

Sub saveRTDinfo()
    Dim wbSource As Workbook
    Dim wsRTD As Worksheet
    Dim wsDest As Worksheet
    Dim copyRowEnd As Long, copyColEnd As Long
    Dim destRowStart As Long
    Dim copyRowStart As Long, copyColStart As Long
    
    ' 绑定到当前工作簿（WB1）
    Set wbSource = ThisWorkbook
    ' 绑定源工作表和目标工作表
    On Error GoTo ErrorHandler
    Set wsRTD = wbSource.Sheets("RTD")
    Set wsDest = wbSource.Sheets(destSheet) ' destSheet需确保在其他地方正确定义
    
    ' 获取源数据的最后一行和最后一列
    copyRowEnd = wsRTD.Cells(wsRTD.Rows.Count, 1).End(xlUp).Row
    copyColEnd = wsRTD.Cells.Find(What:="*", _
                    After:=wsRTD.Range("A1"), _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious).Column
    
    ' 定义复制范围（从第2行第1列开始）
    copyRowStart = 2
    copyColStart = 1
    
    ' 获取目标工作表的第一个空行
    destRowStart = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 1
    
    ' 直接复制值，无需Copy/PasteSpecial（更高效）
    wsDest.Range(wsDest.Cells(destRowStart, copyColStart), _
                 wsDest.Cells(destRowStart + copyRowEnd - copyRowStart, copyColEnd)).Value = _
                 wsRTD.Range(wsRTD.Cells(copyRowStart, copyColStart), _
                             wsRTD.Cells(copyRowEnd, copyColEnd)).Value
    
    Exit Sub
    
ErrorHandler:
    MsgBox "执行出错：" & Err.Description & vbCrLf & "错误编号：" & Err.Number
    ' 可根据需求添加错误日志等操作
End Sub

关键优化点 #

• 绑定对象：所有工作表、单元格操作都明确绑定到ThisWorkbook（WB1），彻底避免激活工作簿切换带来的问题。
• 移除Select/Activate：直接操作对象，代码更稳定、高效。
• 替换Copy/Paste：直接赋值单元格值，比剪贴板操作更快且避免剪贴板冲突。
• 规范错误处理：添加ErrorHandler块，能明确捕获并提示错误信息。
• 变量类型明确：原代码中copyRowEnd等变量未声明类型，默认是Variant，修改后明确为Long类型，避免类型转换问题。

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