求高效VBA代码：将Excel工作表复制为值存入同工作簿新标签

原代码的问题分析 #

• 使用Worksheet.Copy会复制工作表的所有元素（公式、数据验证、条件格式、链接等），大表场景下会显著增加处理时间
• 存在语法错误：shtDeskEP (2nd excel tab)是无效引用，需明确指定目标位置
• 收尾设置错误：未正确恢复ScreenUpdating为True，CutCopyMode=True属于冗余操作，会额外占用内存

优化后的代码 #

Sub CopySheetAsValues()
    ' 关闭Excel冗余功能，提升运行速度
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False ' 新增：关闭事件触发，避免不必要的回调
        .CutCopyMode = False
    End With

    Dim sourceSheet As Worksheet
    Dim newSheet As Worksheet
    Set sourceSheet = ActiveSheet

    ' 新建工作表，插入到第2个标签之前
    Set newSheet = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(2))

    ' 复制值和格式：选择性粘贴比整表复制高效
    sourceSheet.UsedRange.Copy
    With newSheet.Range(sourceSheet.UsedRange.Address)
        .PasteSpecial xlPasteValuesAndNumberFormats ' 粘贴值与数字格式
        .PasteSpecial xlPasteFormats ' 粘贴单元格格式
    End With
    Application.CutCopyMode = False ' 释放剪贴板内存

    ' 用A1的值设置新工作表名称
    If sourceSheet.Range("A1").Value <> "" Then
        On Error Resume Next ' 处理名称重复的异常
        newSheet.Name = sourceSheet.Range("A1").Value
        On Error GoTo 0 ' 恢复默认错误处理
    End If

    ' 恢复Excel默认设置
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
End Sub

核心优化点说明 #

• 精准复制内容：只保留值和必要格式，跳过公式、数据验证等非归档必需的元素，大幅降低Excel的处理负载
• 关闭事件触发：新增.EnableEvents = False，避免复制过程中触发工作表事件（如Worksheet_Change）拖慢速度
• 明确对象引用：用ThisWorkbook.Sheets(2)指定插入位置，修复原代码的无效引用问题
• 规范恢复设置：确保所有临时关闭的功能都恢复默认状态，不影响后续操作

额外提速建议 #

• 若无需保留格式，可直接赋值：newSheet.UsedRange.Value = sourceSheet.UsedRange.Value，这是最快的复制方式
• 尽量避免使用ActiveSheet，明确指定源工作表（如Set sourceSheet = ThisWorkbook.Sheets("数据源")），代码更稳定且效率更高
• 若工作表包含大量条件格式，可跳过格式复制，只保留值，进一步提升速度

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