使用粘贴值并保留格式将工作表复制到新工作簿 [英] Copy a worksheet to a new workbook with paste values and keeping formats

问题描述

我正在尝试将一个工作表复制到新工作簿，将所有公式粘贴为值，同时保留所有格式，工作表名称等.新文件名应为"University"，并与原始文件存储在同一位置. 我一直在为此苦苦挣扎，因为它一直返回

I am trying to copy one worksheet to a new workbook, pasting all formulas as values while remaining all formats, sheetname, etcetera. The new file name should be "University" and stored on the same location as the original file. I have been struggling with this, as it keeps returning an

错误1004:Range类的PasteSpecial方法失败"

"Error 1004: PasteSpecial method of Range class failed"

使用我当前(已复制)的脚本:

with my current (copied) script:

Sub new_workbook()

Dim Output As Workbook
Dim FileName As String

Set Output = Workbooks.Add
Application.DisplayAlerts = False

ThisWorkbook.Worksheets("Report").Copy

Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False

FileName = ThisWorkbook.Path & "\" & "University.xlsx"
Output.SaveAs FileName

End Sub

没有指定 Before 或 After 的

推荐答案

Worksheet.Copy会创建一个新的工作簿，因此只需那个.

Worksheet.Copy with no Before or After specified creates a new workbook, so just work off of that.

文档中的详细信息:

如果您未指定 Before 或 After ，Microsoft Excel将创建一个新的工作簿，其中包含复制的Worksheet对象.新创建的工作簿具有Application.ActiveWorkbook属性，并包含一个工作表.

If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied Worksheet object. The newly created workbook holds the Application.ActiveWorkbook property and contains a single worksheet.

Sub new_workbook()

    ThisWorkbook.Worksheets("Report").Copy '<-- creates a new workbook with a copy of your sheet

    Dim Output as Workbook
    Set Output = ActiveWorkbook

    With Output.Worksheets(1).UsedRange
        .Value = .Value '<-- changes all formulas to values
    End With

    Dim FileName As String
    FileName = ThisWorkbook.Path & "\University.xlsx"

    Application.DisplayAlerts = False
    Output.SaveAs FileName

End Sub

这篇关于使用粘贴值并保留格式将工作表复制到新工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！