vba VBA使用范围选择多个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26861958/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Selecting multiple sheets using a range
提问by h.l.m
I would I have some sheet names in cells C2 to C5, which are to be dynamic and I would like to select them those at the same time using VBA
我希望在单元格 C2 到 C5 中有一些工作表名称，它们是动态的，我想使用 VBA 同时选择它们
But the only way I have found so far relates to using arrays and "hard-coding" the sheet names in, and would like instead to use Ranges
但到目前为止我发现的唯一方法与使用数组和“硬编码”工作表名称有关，而是想使用范围
here is the code I have tried so far
这是我迄今为止尝试过的代码
Sub ssheets()
Worksheets(Array("Sheet2", "Sheet3","Sheet4","Sheet5")).Select
End Sub
I would ideally like something that uses Range("C2:C5")so that I can dynamically select the relevant sheets without having to type in "Sheet2", "Sheet3","Sheet4","Sheet5" etc into the VBA code
理想情况下，我希望使用某些东西，Range("C2:C5")以便我可以动态选择相关工作表，而无需在 VBA 代码中输入“Sheet2”、“Sheet3”、“Sheet4”、“Sheet5”等
Thanks
谢谢
回答by Axel Richter
The sheet names array has to be of type Variant containing a one dimensional array. The Range("C2:C5") returns a two dimensional array. To use this as sheet names array, you have to transpose it.
工作表名称数组必须是包含一维数组的 Variant 类型。Range("C2:C5") 返回一个二维数组。要将其用作工作表名称数组，您必须对其进行转置。
Sub ssheets()
Dim oWS As Worksheet
Dim aSheetnames As Variant
Set oWS = Worksheets(1)
aSheetnames = oWS.Range("C2:C5")
aSheetnames = Application.WorksheetFunction.Transpose(aSheetnames)
Worksheets(aSheetnames).Select
End Sub
回答by ttaaoossuu
Try this:
尝试这个：
Sub Macro1()
Dim sheetArray() As String
Dim i As Integer
i = 0
For Each c In Range("C2:C5").Cells
ReDim Preserve sheetArray(0 To i)
sheetArray(i) = c.Value
i = i + 1
Next
Sheets(sheetArray).Select
End Sub
You may also consider adding verification if the sheet with that name exists before adding it to array.
您也可以考虑在将其添加到数组之前添加具有该名称的工作表是否存在验证。
