将值从多个工作表复制到新的工作簿 [英] Copy values only to new workbook from multiple worksheets

问题描述

假设我有一个 workbook1.xlsm ，带有多个工作表，并且包含各种公式。我想创建一个新的 workbook2.xlsx ，它将与 workbook1 完全相同 但是所有的单元格都是值而不是公式。

我有这个宏从 workbook1 ：

  Sub nowe（）
 
 Dim输出为Workbook 
 Dim FileName As String 
 
设置输出= Workbooks.Add 
 Application.DisplayAlerts = False 
 
 ThisWorkbook.Worksheets（Przestoje）。Cells.Copy 
 
 Selection.PasteSpecial Paste：= xlPasteValues，_ 
操作：= xlNone，SkipBlanks：= True，Transpose：= False 
 
 Selection.PasteSpecial粘贴：= xlPasteFormats 
 
 FileName = ThisWorkbook.Path& \& worksheet2.xlsx
 Output.SaveAs FileName 
 
 End Sub 
  

但问题是它只复制一个工作表，并不像在 worksheet1 中那样命名。我不能弄清楚。

另一个问题是，以后打开 worksheet2 。我不想这样做。

如何解决这些问题？

解决方案

我会尽可能简单地做到这一点，而不需要创建新的工作簿和复印表。

几个简单的步骤：考虑到本工作簿>>对于本工作簿>中的每个工作表在工作表中使用范围的复制+粘贴值>>另存为xlsx类型>的新工作簿打开基本工作簿>>最后关闭我们创建的一个。

代码将很简单，如下所示：

  Sub nowe_poprawione（）
 
 Dim输出作为工作簿
 Dim Current As String 
 Dim FileName As String 
 
 Set Output = ThisWorkbook 
 Current = ThisWorkbook.FullName 
 
 Application.DisplayAlerts = False 
 
 Dim SH As Worksheet 
对于每个SH输出.Worksheets 
 
 SH.UsedRange.Copy 
 SH.UsedRange.PasteSpecial xlPasteValues，_ 
操作：= xlNone，SkipBlanks：= True，Transpose：= False 
 
下一个
 
 FileName = ThisWorkbook.Path& \& worksheet2.xlsx
 Output.SaveAs FileName，XlFileFormat.xlOpenXMLWorkbook 
 Workbooks.Open当前
 Output.Close 
 Application.DisplayAlerts = True 
 End Sub 
  

Suppose I have a workbook1.xlsm with multiple worksheets and full of various formulas. I want to create a new workbook2.xlsx which would look exactly the same as workbook1 but in all the cells would be values instead of formulas.

I have this macro to copy one sheet from workbook1:

Sub nowe()

Dim Output As Workbook
Dim FileName As String

Set Output = Workbooks.Add
Application.DisplayAlerts = False

ThisWorkbook.Worksheets("Przestoje").Cells.Copy

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

Selection.PasteSpecial Paste:=xlPasteFormats

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

End Sub

but the problem is it copies only one worksheet and does not name it like it was in worksheet1. I cannot figure it out.

Yet another problem is that worksheet2 is being opened afterwards. I do not want to do this.

How can I solve these problems?

解决方案

I would do that as simply as possibly, without creating new workbook and copying sheets to it.

Few simple steps: taking into consideration thisworkbook >> for each worksheet within thisworkbook >> copy+paste values of used range within worksheet >> save as new workbook as xlsx type >> open back base workbook >> and finally close one we created.

The code will be simple and looks as follows:

Sub nowe_poprawione()

    Dim Output As Workbook
    Dim Current As String
    Dim FileName As String

    Set Output = ThisWorkbook
    Current = ThisWorkbook.FullName

    Application.DisplayAlerts = False

    Dim SH As Worksheet
    For Each SH In Output.Worksheets

        SH.UsedRange.Copy
        SH.UsedRange.PasteSpecial xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    Next

    FileName = ThisWorkbook.Path & "\" & "worksheet2.xlsx"
    Output.SaveAs FileName, XlFileFormat.xlOpenXMLWorkbook
    Workbooks.Open Current
    Output.Close
    Application.DisplayAlerts = True
End Sub

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