Excel VBA复制替换命名范围遇类型不匹配错误，求修正及列限制方案

你的代码出现类型不匹配错误，大概率是因为目标工作簿中不存在同名的DBrange命名范围，或者引用命名范围的方式没有考虑到作用域（比如命名范围是工作簿级还是工作表级）。另外我也帮你实现了仅复制A:AD列的需求，下面是修正后的代码和详细说明：

修正后的代码 #

Sub ImportLogInfo()
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range
    Dim targetSheet As Worksheet
    Dim sourceSheet As Worksheet
    
    ' 初始化工作簿和工作表对象
    Set wkbCrntWorkBook = ActiveWorkbook
    Set targetSheet = wkbCrntWorkBook.Sheets("Log")
    Set wkbSourceBook = Workbooks.Open(Filename:="C:\Users\Documents\Proposal Log Feeder\Log Feeder A.xlsb", ReadOnly:=True)
    Set sourceSheet = wkbSourceBook.Sheets("Log")
    
    ' 提取源命名范围中的A:AD列（处理无交集的情况）
    On Error Resume Next
    Set rngSourceRange = Intersect(sourceSheet.Range("DBrange"), sourceSheet.Range("A:AD"))
    On Error GoTo 0
    
    ' 检查源范围是否有效
    If rngSourceRange Is Nothing Then
        MsgBox "源工作簿未找到DBrange命名范围，或该范围与A:AD列无交集", vbExclamation
        wkbSourceBook.Close False
        Exit Sub
    End If
    
    ' 处理目标命名范围：存在则复用，不存在则创建
    On Error Resume Next
    Set rngDestination = targetSheet.Range("DBrange")
    On Error GoTo 0
    
    If rngDestination Is Nothing Then
        ' 目标无此命名范围时，创建对应大小的范围并绑定命名
        Set rngDestination = targetSheet.Range("A1").Resize(rngSourceRange.Rows.Count, rngSourceRange.Columns.Count)
        targetSheet.Names.Add Name:="DBrange", RefersTo:=rngDestination
    Else
        ' 目标范围存在时，确保大小与源范围匹配，更新命名范围引用
        If rngDestination.Rows.Count <> rngSourceRange.Rows.Count Or rngDestination.Columns.Count <> rngSourceRange.Columns.Count Then
            Set rngDestination = rngDestination.Resize(rngSourceRange.Rows.Count, rngSourceRange.Columns.Count)
            targetSheet.Names("DBrange").RefersTo = rngDestination
        End If
    End If
    
    ' 复制源范围的内容（可根据需求替换为xlPasteValues仅复制值）
    rngSourceRange.Copy
    rngDestination.PasteSpecial Paste:=xlPasteAll
    
    ' 自动调整列宽
    rngDestination.EntireColumn.AutoFit
    
    ' 清理剪贴板，避免Excel保持复制状态
    Application.CutCopyMode = False
    
    ' 关闭源工作簿，不保存修改
    wkbSourceBook.Close False
End Sub

关键修正点说明 #

1. 错误处理避免类型不匹配：
   原代码中如果目标工作簿没有DBrange命名范围，Set rngDestination = ...会直接抛出类型不匹配错误。这里通过On Error Resume Next捕获错误，并判断范围是否存在，避免了这个问题。

2. 实现仅复制A:AD列：
   使用Intersect函数提取源命名范围与A:AD列的交集，完美满足你只复制指定列的需求。如果源范围和A:AD列没有交集，会弹出提示并终止流程。

3. 动态处理目标命名范围：

   • 如果目标没有DBrange，自动创建对应大小的范围并绑定命名；
   • 如果目标范围存在，但大小和源范围不一致，会调整目标范围大小并更新命名范围的引用，避免复制时出现尺寸不匹配的问题。

4. 更安全的复制方式：
   使用PasteSpecial代替直接的Copy，你可以根据需求修改参数（比如xlPasteValues只复制值，xlPasteFormats只复制格式），比直接复制更灵活可靠。

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