请求协助：VBA新建工作表时将数据粘贴为值而非复制格式

原代码通过origSht.Cells.Copy Destination:=destSht.Cells会完整复制原工作表的格式、公式、条件格式等所有属性，要实现仅粘贴单元格的值，可通过以下两种方式修改：

方法1：使用PasteSpecial粘贴值（直观易用） #

这种方式保留复制粘贴的操作逻辑，明确指定仅粘贴值：

Sub COPY_SHEET_2_NEW_SHEET()

    Dim origSht             As Worksheet
    Dim destSht             As Worksheet
    Dim newSheetName        As String

    On Error GoTo eHandle

    Set origSht = ActiveSheet

    newSheetName = InputBox("What Would You Like to Call the New Sheet?")
    ' 先创建工作表再命名，避免命名失败时残留空白工作表
    Set destSht = Sheets.Add
    destSht.Name = newSheetName

    ' 复制原表数据后，仅粘贴值到新表
    origSht.Cells.Copy
    destSht.Cells.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False ' 清除剪贴板复制状态

Exit Sub

eHandle:
    ' 错误处理：删除未正确命名的空白工作表
    If Not destSht Is Nothing Then
        Application.DisplayAlerts = False
        destSht.Delete
        Application.DisplayAlerts = True
    End If
    MsgBox "Invalid sheet name or you canceled. Please try again."
    Set origSht = Nothing
    Set destSht = Nothing

End Sub

关键改动说明 #

• 调整工作表创建逻辑：先新建工作表再命名，避免输入无效名称时留下无意义的空白工作表
• 替换直接复制目标为Copy+PasteSpecial xlPasteValues，明确仅粘贴单元格的值
• 添加Application.CutCopyMode = False清除剪贴板状态，避免后续操作受影响
• 优化错误处理：自动清理未正确命名的残留工作表

方法2：数组赋值（高效适合大数据） #

如果原工作表数据量较大，直接通过数组读取和写入值会比复制粘贴更快，且不依赖剪贴板：

Sub COPY_SHEET_2_NEW_SHEET()

    Dim origSht             As Worksheet
    Dim destSht             As Worksheet
    Dim newSheetName        As String
    Dim dataRange           As Range
    Dim dataArray           As Variant

    On Error GoTo eHandle

    Set origSht = ActiveSheet
    ' 仅读取原表中有数据的区域，避免处理大量空单元格
    Set dataRange = origSht.UsedRange
    dataArray = dataRange.Value

    newSheetName = InputBox("What Would You Like to Call the New Sheet?")
    Set destSht = Sheets.Add
    destSht.Name = newSheetName

    ' 将数组中的值写入新工作表对应位置
    destSht.Cells(1, 1).Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray

Exit Sub

eHandle:
    If Not destSht Is Nothing Then
        Application.DisplayAlerts = False
        destSht.Delete
        Application.DisplayAlerts = True
    End If
    MsgBox "Invalid sheet name or you canceled. Please try again."
    Set origSht = Nothing
    Set destSht = Nothing
    Set dataRange = Nothing

End Sub

优势说明 #

• 仅处理原表中已使用的区域，减少不必要的操作
• 数组读写比复制粘贴更高效，尤其适合十万行级别的大数据
• 不占用剪贴板，避免与其他操作冲突

内容的提问来源于stack exchange，提问作者John Sayers Jr