如何复制值与条件格式时排除名称管理器定义并处理删除异常？

我需要将源工作表的所有数据值和条件格式复制到另一个工作簿的工作表中，目前使用以下代码可以实现功能，但存在问题：

wsDestinationSheet.Cells.ClearContents
wsDestinationSheet.Cells.FormatConditions.Delete
        
' 复制源区域到剪贴板
Range("A1:O19").Copy
        

wsDestinationSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
wsDestinationSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
wsDestinationSheet.UsedRange.EntireColumn.AutoFit

源工作表部分列使用下拉菜单，使用xlPasteFormats粘贴时，不仅会复制条件格式，还会同步复制源工作簿的名称管理器定义。首次复制看似正常，但二次复制时会弹出提示，要求选择覆盖或保留目标工作簿中已有的同名定义。我不需要目标工作簿保留这些名称管理器定义，想知道如何在复制格式时排除它们，同时确保条件格式和单元格格式都能正常复制。

编辑1：尝试删除名称管理器条目遇到的问题 #

我尝试用以下代码删除目标工作簿的名称管理器条目：

For i = wbDestinationWorkbook.Names.Count To 1 Step -1
wbDestinationWorkbook.Names(i).Delete         
Next

但发现wbDestinationWorkbook.Names.Count返回5，而手动查看目标工作簿的名称管理器只能看到4个条目。循环删除到第1个条目时抛出错误，查看wbDestinationWorkbook.Names(1)发现其名称为_xlfn.XLOOKUP，值为=#NAME?。请问这是有效条目吗？如果不是，如何在删除前验证每个名称条目是否可删除？

解决方案 #

方案1：拆分粘贴，避免引入名称管理器 #

不要使用xlPasteFormats一次性粘贴所有格式，改用更精准的粘贴选项拆分操作，既保留需要的格式，又不会复制名称管理器：

wsDestinationSheet.Cells.ClearContents
wsDestinationSheet.Cells.FormatConditions.Delete

Range("A1:O19").Copy

' 1. 粘贴值
wsDestinationSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
' 2. 粘贴条件格式
wsDestinationSheet.Range("A1").PasteSpecial Paste:=xlPasteConditionalFormats
' 3. 粘贴单元格基础格式（字体、对齐、边框等）
wsDestinationSheet.Range("A1").PasteSpecial Paste:=xlPasteCellFormats
' 4. 自动调整列宽
wsDestinationSheet.UsedRange.EntireColumn.AutoFit

如果不需要保留源工作表的下拉菜单（数据验证），可以完全避免复制相关内容；如果需要保留下拉菜单但不需要名称，可以单独处理数据验证，不依赖剪贴板粘贴。

方案2：安全删除目标工作簿的非系统名称 #

针对编辑1中的问题，_xlfn.XLOOKUP是Excel的系统兼容性隐藏名称，用于兼容旧版本中不支持的函数，属于内置不可删除的条目，因此需要过滤这类名称再执行删除：

Dim nm As Name
For Each nm In wbDestinationWorkbook.Names
    ' 只删除用户可见、非系统前缀的有效名称
    If nm.Visible _
        And Not Left(nm.Name, 6) = "_xlfn." _
        And Not Left(nm.Name, 6) = "_xlws." _
        Then
        On Error Resume Next ' 捕获删除时的异常（如被保护的名称）
        nm.Delete
        On Error GoTo 0
    End If
Next nm

判断逻辑说明：

• nm.Visible：只处理用户在名称管理器中可见的名称，跳过系统隐藏名称
• Left(nm.Name, 6) = "_xlfn."：跳过函数兼容性前缀的系统名称
• Left(nm.Name, 6) = "_xlws."：跳过工作表相关的系统隐藏名称
• 加入错误捕获，避免因权限或系统保护导致代码中断

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