VBA复制彩色单元格至另一文件同名工作表同区域报错求助

错误原因分析 #

报错行With ActiveSheet.Range(SelectedRng)触发"应用程序定义或对象定义错误"，核心问题是：

• SelectedRng是绑定到源工作表的Range对象，切换到目标工作簿的工作表后，无法直接将源工作表的Range对象作为参数传给目标工作表的Range方法，两者属于不同的工作表对象上下文。
• 原代码过度依赖Select和Activate操作，这类操作不仅容易引发对象上下文混乱，还会降低代码执行效率。

修正后的代码 #

Sub CopyColoredCellsToSameSheet()
    Const rgAddress As String = "A1:AZ300"
    Const cIndex As Long = 37
    Const targetWBName As String = "Workbook2.xlsx"
    
    Dim sourceWS As Worksheet
    Dim targetWB As Workbook
    Dim targetWS As Worksheet
    Dim rg As Range
    Dim coloredRng As Range
    Dim cell As Range
    Dim coloredRangeAddress As String
    
    Application.ScreenUpdating = False
    
    ' 初始化源工作表
    Set sourceWS = ActiveSheet
    Set rg = sourceWS.Range(rgAddress)
    
    ' 查找ColorIndex=37的单元格
    For Each cell In rg.Cells
        If cell.Interior.ColorIndex = cIndex Then
            If coloredRng Is Nothing Then
                Set coloredRng = cell
            Else
                Set coloredRng = Union(coloredRng, cell)
            End If
        End If
    Next cell
    
    ' 检查是否找到目标单元格
    If coloredRng Is Nothing Then
        MsgBox "指定范围内无ColorIndex=37的单元格。", vbExclamation
        GoTo Cleanup
    End If
    
    ' 保存源区域的地址字符串
    coloredRangeAddress = coloredRng.Address
    
    ' 检查目标工作簿是否打开
    On Error Resume Next
    Set targetWB = Workbooks(targetWBName)
    On Error GoTo 0
    If targetWB Is Nothing Then
        MsgBox "目标工作簿 " & targetWBName & " 未打开。", vbCritical
        GoTo Cleanup
    End If
    
    ' 检查目标工作表是否存在
    On Error Resume Next
    Set targetWS = targetWB.Worksheets(sourceWS.Name)
    On Error GoTo 0
    If targetWS Is Nothing Then
        MsgBox "目标工作簿中不存在名为 " & sourceWS.Name & " 的工作表。", vbCritical
        GoTo Cleanup
    End If
    
    ' 复制粘贴值到对应位置（无需激活/选中）
    coloredRng.Copy
    targetWS.Range(coloredRangeAddress).PasteSpecial xlPasteValues
    
Cleanup:
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

关键改进点 #

• 用地址字符串替代Range对象传递跨工作表的区域定位，避免对象上下文冲突
• 移除所有Select和Activate操作，直接通过工作表/工作簿对象引用完成操作，提升代码稳定性和效率
• 增加错误检查：验证目标工作簿是否打开、目标工作表是否存在，避免运行时意外报错
• 增加清理块，确保剪贴板状态和屏幕更新恢复正常

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