仅在另一个工作簿中保存一些工作表 [英] Saving only some sheets in another Workbook

问题描述

我使用一个宏来仅保存一些新的工作簿中的预定义工作表。我使用一个 userform 要求新文件的名称，创建它并打开它，然后从旧的文件到新文件逐张复制和粘贴工作表。

这已经花了很多时间来运行，而且随着我的工作表中的越来越多的数据复制和粘贴，这将会变得更糟。

是否有其他方式继续？

这是我的代码：

WB2 是旧书， Ws 是旧书中的工作表， WB 是新书， Dico_export 是一个包含要复制的工作表名称的字典。

$
WB2.Worksheets（WS.Name）.Copy after：= WB.Sheets（1 + i）
如果WS.Name<> Limites LPG然后
tabl（i）= WS.Name
End If
i = i + 1
End If
Next

解决方案

什么是tabl（i）变量？此外，如果要实现数组捕获工作表数据，然后复制到另一个工作簿，则代码的运行速度要快得多。
创建一个变量以保存对新工作簿的引用（要复制到），并将新工作表添加到新书中。
对于您复制的每个工作表，将新工作表添加到新书，设置名称属性等，然后将现有工作表数据添加到数组变量（使用.Value2属性更快），并将其复制到新表...

  Dim x（）
 Dim WB As Workbook，WB2 As Workbook 
 Dim newWS作为工作表，WS作为工作表
 Dim i As Long，r As Long，c As Long 
i = 1 
 
对于每个WS在WB2.Worksheets 
如果Dico_Export .Exists（WS.Name）然后
如果WS.Name<> Limites LPG然后
x = WS.Range（A1：N5000）。Value2需要调整范围以复制
设置newWS = WB.Worksheets.Add（之后：= WB.Sheets 1& i））调整以适应您的情况
使用newWS 
 .Name =将新书中的工作表命名为
对于r = LBound（x，1）对于UBound（x，1）
对于c = LBound（x，2）到UBound（x，2）
 .Cells（r，c）= x（r，c）
下一步
下一个
结束
删除x 
设置newWS = Nothing 
''tabl（i）= WS.Name（？）
 End If 
结束如果
下一个
  

I want to use a macro to save only some predefined sheets in a new workbooks.

I use a userform to ask for the name of the new file, create it and open it, then copy and paste sheets one by one from the old to the new file.

This already take a lot of time to run, and this will get worse as I get more and more data in my sheets to copy and paste.

Is there another way to proceed ?

Here is my code:

WB2 is the old book, Ws is the worksheet in the old book, WB is the new book, Dico_export is a dictionary containing the name of sheets to be copied.

For Each WS In WB2.Worksheets
    If Dico_Export.Exists(WS.Name) Then
        WB2.Worksheets(WS.Name).Copy after:=WB.Sheets(1 + i)
        If WS.Name <> "Limites LPG" Then
        tabl(i) = WS.Name
        End If
        i = i + 1
    End If
Next

解决方案

What is the tabl(i) variable?? Also, your code would run much faster if you were to implement an Array to capture the worksheet data and then copy to another workbook. Create a variable to hold the reference to the new workbook (to be copied to) and for the new worksheet to add to the new book. For each sheet that you copy add a new worksheet to the new book, setting name properties, etc. then add the existing sheet data to the array variable (use .Value2 property as it is faster) and copy it to the new sheet...

Dim x()
Dim WB As Workbook, WB2 As Workbook
Dim newWS As Worksheet, WS As Worksheet
Dim i As Long, r As Long, c As Long
i = 1

For Each WS In WB2.Worksheets
        If Dico_Export.Exists(WS.Name) Then
            If WS.Name <> "Limites LPG" Then
               x = WS.Range("A1:N5000").Value2 ''need to adjust range to copy
               Set newWS = WB.Worksheets.Add(After:=WB.Sheets(1 & i))    ''adjust to suit         your     situation
               With newWS
                   .Name = "" '' name the worksheet in the new book
                   For r = LBound(x, 1) To UBound(x, 1)
                    For c = LBound(x, 2) To UBound(x, 2)
                        .Cells(r, c) = x(r, c)
                    Next
                   Next
               End With
               Erase x
               Set newWS = Nothing
            '' tabl(i) = WS.Name (??)
            End If
        End If
Next

这篇关于仅在另一个工作簿中保存一些工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！