VB.net中Range.PasteSpecial()报错，求Excel值与格式复制解决方案

报错原因 #

你遇到的Microsoft Excel cannot paste the data错误主要来自三个问题：

1. 删除工作表的循环逻辑错误：使用For Each遍历工作表并删除时，工作表集合会动态变化，导致循环异常，后续操作的上下文被破坏。
2. 依赖ActiveSheet的不稳定操作：跨工作簿操作时，ActiveSheet的状态可能因为Excel的自动切换而改变，导致粘贴目标错误。
3. 复制后上下文切换导致剪贴板失效：执行Copy()后立即添加新工作表，Excel的操作上下文切换会清空剪贴板内容，无法完成粘贴。

修正后的代码 #

以下代码修复了上述问题，同时实现值与格式的完整复制：

Sub CopyData()
    Dim xlApp As New Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlFileName As String
    Dim OpenFile As New OpenFileDialog
    Dim wsActvSh As Worksheet = Globals.ThisWorkbook.ActiveSheet
    Dim targetWs As Excel.Worksheet ' 显式声明目标工作表，避免依赖ActiveSheet

    xlApp.DisplayAlerts = False

    If OpenFile.ShowDialog() <> DialogResult.Cancel Then
        xlFileName = OpenFile.FileName
        xlWb = xlApp.Workbooks.Open(xlFileName)
    Else
        xlApp.Quit()
        Exit Sub
    End If

    xlApp.Visible = True

    ' 修复：倒序删除多余工作表，避免集合枚举异常
    Dim i As Integer
    For i = xlWb.Worksheets.Count To 2 Step -1
        xlWb.Worksheets(i).Delete()
    Next i

    For Each ws As Worksheet In Globals.ThisWorkbook.Worksheets
        If ws.Visible = True And Mid(ws.Name, 5, 2) = Mid(wsActvSh.Name, 11, 2) Then
            ' 1. 先添加并命名目标工作表，避免复制后切换上下文
            Set targetWs = xlWb.Worksheets.Add(After:=xlWb.Worksheets(xlWb.Worksheets.Count))
            targetWs.Name = Mid(ws.Name, 5)
            
            ' 2. 复制源范围，直接指定目标范围进行粘贴，无需依赖剪贴板的全局状态
            ws.Range("a:i").Copy
            ' 粘贴值和格式：先粘贴值，再粘贴格式
            targetWs.Range("a1").PasteSpecial(XlPasteType.xlPasteValues)
            targetWs.Range("a1").PasteSpecial(XlPasteType.xlPasteFormats)
            
            ' 清除剪贴板，避免Excel提示
            Application.CutCopyMode = False
        End If
    Next

    xlApp.DisplayAlerts = True
End Sub

关键修改说明 #

• 倒序删除工作表：从最后一个工作表往前删，避免遍历过程中集合元素变化导致的循环错误。
• 显式引用目标工作表：直接用变量保存新建的工作表，不再依赖ActiveSheet，确保粘贴目标准确。
• 调整操作顺序：先创建目标工作表，再执行复制粘贴，避免上下文切换清空剪贴板。
• 分两次粘贴：先用xlPasteValues粘贴值，再用xlPasteFormats粘贴格式，确保两者都被复制；若需要保留主题样式，也可替换为XlPasteType.xlPasteAllUsingSourceTheme一次性完成值和格式的复制。

替代方案（无需剪贴板） #

如果不想依赖剪贴板，也可以通过直接赋值结合格式复制的方式实现：

' 复制值
targetWs.Range("a:i").Value = ws.Range("a:i").Value
' 复制格式
ws.Range("a:i").Copy
targetWs.Range("a:i").PasteSpecial(XlPasteType.xlPasteFormats)
Application.CutCopyMode = False

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