Excel 2010 VBA:如何将工作表数组存储为变量? [英] Excel 2010 VBA: How to store an array of worksheets as a variable?

问题描述

我正在尝试使用我的工作表的多个数组，可以使用它们在我的代码中调用.

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!!

推荐答案

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

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.

它将归结为:

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

如果要预定义数组，也很容易做到.这些将只需要包含工作表的名称.可以使用Variable变量创建一个数组:

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:

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

并在.Sheets().Copy中使用它:

ThisWorkbook.Sheets(ArrayOne).Copy

这篇关于Excel 2010 VBA:如何将工作表数组存储为变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！