Excel 2010 VBA：如何将工作表数组存储为变量？

Excel 2010 VBA: How to store an array of worksheets as a variable?

提问by user2437803

I am trying to have several arrays of my worksheets that I can call up in my code using.

我正在尝试使用我可以在我的代码中调用的几个工作表数组。

ThisWorkbook.Sheets(Array("Sheet1", "Sheet3"))
ThisWorkbook.Sheets(Array("Sheet2", "Sheet5"))

I am wondering if there is anyway to set up a variable similar to the following:

我想知道是否有设置类似于以下的变量：

Dim ArrayOne As String
Dim ArrayTwo As String

ArrayOne = ThisWorkbook.Sheets(Array("Sheet1", "Sheet3"))
ArrayTwo = ThisWorkbook.Sheets(Array("Sheet2", "Sheet5"))

ArrayOne 'Call this Array then save

Filename:="C:\Data\testfile.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _,
CreateBackup:=False 

ArrayTwo 'Call this array then save

Filename:="C:\Data\testfile.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _,
CreateBackup:=False 

Please let me know if you can help me!!

如果你能帮助我，请告诉我！！

回答by Ripster

Here is an example of how arrays in VBA work:

以下是 VBA 中数组如何工作的示例：

Sub Example()
    Dim ArrayOne() As String
    Dim ArrayTwo() As String
    Dim ArrayThree As Variant
    Dim i As Long

    ReDim ArrayOne(1 To Sheets.Count)
    ReDim ArrayTwo(1 To 2)

    For i = 1 To Sheets.Count
        ArrayOne(i) = Sheets(i).Name
    Next

    ArrayTwo(1) = "Sheet1"
    ArrayTwo(2) = "Sheet2"

    ArrayThree = Array("Sheet1", "Sheet3")
End Sub

Now from what I understand you do not want to use arrays. You can reference worksheets in your workbook like this:

现在据我所知，您不想使用数组。您可以像这样在工作簿中引用工作表：

Sheets("SheetName") 'SheetName is the name of your sheet
Sheets(1)           '1 = sheet index

One way to copy sheets to a new workbook to be saved is:

将工作表复制到要保存的新工作簿的一种方法是：

Sub Example()
    Dim wkbk As Workbook

    ThisWorkbook.Sheets("Sheet1").Copy
    Set wkbk = ActiveWorkbook
    ThisWorkbook.Sheets("Sheet3").Copy After:=wkbk.Sheets(wkbk.Sheets.Count)

    wkbk.SaveAs FileName:="C:\New Excel Book.xlsx", _
                FileFormat:=xlOpenXMLWorkbook
    wkbk.Close
End Sub

回答by K_B

Try using the record macro functionality. It will allow you to select multiple sheets and then copy them into a new book. Next save that book and you are there. Now tinker with the code to get it to work specifically the way you want.

尝试使用记录宏功能。它将允许您选择多张纸，然后将它们复制到一本新书中。接下来保存那本书，你就在那里。现在修改代码，让它按照你想要的方式工作。

It will come down to:

它将归结为：

ThisWorkbook.Sheets(Array("Sheet1", "Sheet3")).Copy
ActiveWorkbook.SaveAs ...

If you want to predefine the arrays, thats is easily done as well; those will just have to contain the names of the sheets. An Array can be created by using a Variable variable:

如果你想预定义数组，那也很容易做到；那些只需要包含工作表的名称。可以使用 Variable 变量创建数组：

Dim ArrayOne as Variant
ArrayOne = Array("Sheet1", "Sheet3")

And use that in the .Sheets().Copy:

并在.Sheets().Copy：

ThisWorkbook.Sheets(ArrayOne).Copy

回答by Arthur

I had a similar problem trying to create a dynamic array (not knowing how many sheets there was for me to deal with). I simply used this:

我在尝试创建动态数组时遇到了类似的问题（不知道我要处理多少张纸）。我只是用这个：

Sub copyArrayOfSheets()

Dim loopArray() As Variant

ReDim Preserve loopArray(1 To 1)
loopArray(1) = "Sheet1" ' a Sheet I know I need to export

j = 1
For Each loopSheet In ThisWorkbook.Sheets
    If loopSheet.Name <> "Sheet1" Then
        theName = loopSheet.Name
        j = j + 1
        ReDim Preserve loopArray(1 To j)
        loopArray(j) = theName ' Assign the name of the sheets to j-th position of loopArray() 
    End If
Next loopSheet

Sheets(loopArray()).Copy

Set newBook = ActiveWorkbook    
newBook.Activate

End Sub

Hope this helps in any way...

希望这有任何帮助...

回答by papadopouleas

Following Arthur's solution (last comment), I had a similar problem (thus reached this post) : I was trying to create a dynamic array, which would save a series of sheets within a Workbook in an array and then perform specific actions with that array.

按照 Arthur 的解决方案（最后一条评论），我遇到了类似的问题（因此到达了这篇文章）：我试图创建一个动态数组，它将工作簿中的一系列工作表保存在一个数组中，然后对该数组执行特定操作.

What is different is that, the user defines the sheets' names within a range (column) in excel (they represent scenarios for another macro), however this range may be expanded or shortened.

不同的是，用户在excel中定义一个范围（列）内的表格名称（它们代表另一个宏的场景），但是这个范围可以扩大或缩短。

I use 2 arrays, where i run the loop in the first and save the extension each time to the other array (for transparency reasons). Code:

我使用 2 个数组，我在第一个数组中运行循环并每次将扩展名保存到另一个数组（出于透明度原因）。代码：

Sub testArray()
    Dim a, b As Integer
    scenarios_number = Sheets(sheet1).[c1] - 1 ' (this is where i put the # of scenarios / sheets (-1 is used as i want the array to start from 0))
    a = 0
    Dim Scenarios_array, dimension_array() As Variant
    ReDim Scenarios_array(0 To scenarios_number) '(resize array to match the #'s of scenarios)
    ReDim dimension_array(0 To a)
    For a = 0 To scenarios_number
    Scenarios_array(a) = Range("c8").Offset(a, 0).Value '(this is where my scenarios' names start within sheet1 -- using offset for the loop -- this is why i use -1 above as i want a to start @ 0)
    ReDim Preserve dimension_array(0 To a) ' (expand dimension of 2nd array)
    dimension_array(a) = Scenarios_array(a) ' (save the value in the second array, expaning its dimensions)
    Next
    MsgBox "Get Ready"
    Sheets(dimension_array()).Select
    ActiveWindow.SelectedSheets.Delete
End Sub

Hope that helps :)

希望有帮助:)

回答by Dustin

I also was trying to do this but I found another way

我也试图这样做，但我找到了另一种方法

What i was trying to accomplish was that I have a workbook with multiple sheets and gave them a name. I wanted to select a few sheets and exclude a few sheets that needed to be exported to a different excel file.

我想要完成的是我有一个包含多张工作表的工作簿并给它们起了一个名字。我想选择几张工作表并排除几张需要导出到不同 excel 文件的工作表。

Here is (after a lot of searching and trying) my code

这是（经过大量搜索和尝试后）我的代码

Dustin

达斯汀

Dim ii As Integer 'Counter of worksheets
Dim namefile as string 'Variable for name of the new file
namefile = "NameOfNewFile.xlsx" 'Name of new file

For ii = 1 To ThisWorkbook.Sheets.Count 'Counts from 1 to last sheetnumber
    If Sheets(ii).Name <> "Namesheet1" Then If Sheets(ii).Name <> "Namesheet2" Then Sheets(ii).Select Replace:=False 
    'NameSheet1 and NameSheet2 are being exluded from the new file
Next ii

ActiveWindow.SelectedSheets.Copy 'Copies the selected files

Set NewWb = ActiveWorkbook
NewWb.SaveAs Filename:= _
"C:\Users\" & Environ("UserName") & "\Desktop\" & namefile, FileFormat:=xlOpenXMLWorkbook 
'Saves as xlsx file to desktop
NewWb.Close 'Closes the new file
Set NewWb = Nothing 'Clear NewWb to reduce memory usage