vba 如何从作为所选单元格内容的名称列表创建多个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10423228/
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
How to create multiple sheets from a list of names which are the selected cells' content
提问by NCC
Actually, I only need to help to provide some lessons on Excel VBA not for the code.
实际上，我只需要帮助提供一些有关 Excel VBA 的课程而不是代码。
Problem:
问题：
The main line of code is very basic and although I have no experience in VBA, I understand it
主代码行非常基础，虽然我没有VBA经验，但我理解
Sheets.Add().Name = Name_of_Sheet
If I define a list of name like NameList =Array("SheetA", "SheetB", "SheetC","SheetD")then do the for loop
如果我定义一个名称列表，NameList =Array("SheetA", "SheetB", "SheetC","SheetD")然后执行 for 循环
For I = LBound(NameList) To UBound(NameList)
Sheets.Add().Name = Tabs(I)
Next I
However, many times, there are standard for naming the sheets, going to Visual Basic to edit the macro is not very efficient. I would like to just create the sheet from the cells' content
但是，很多时候，工作表的命名是有标准的，去 Visual Basic 编辑宏不是很有效。我只想从单元格的内容创建工作表
My questions:
我的问题：
1) How does the index of selected data (1D columns, 1D row, or multiple rows x multiple columns) work?
1) 所选数据的索引（一维列、一维行或多行 x 多列）如何工作？
2) How can I access these cells' content?
2) 如何访问这些单元格的内容？
采纳答案by Doug Glancy
Your question is quite open-ended. Here's a start that addresses the "1D Columns" aspect. There are many ways to do this, but I've included a couple of basic VBA constructs, like For Eachand With/End With. You could easily point the variables at other workbooks, worksheets or cells. It's got a little error-handling to address trying use a sheet name that already exists:
你的问题很开放。这是解决“一维列”方面的开始。有很多方法可以做到这一点，但我已经包含了几个基本的 VBA 结构，比如For Each和With/End With。您可以轻松地将变量指向其他工作簿、工作表或单元格。尝试使用已存在的工作表名称时，有一些错误处理：
Sub AddSheets()
Dim cell As Excel.Range
Dim wsWithSheetNames As Excel.Worksheet
Dim wbToAddSheetsTo As Excel.Workbook
Set wsWithSheetNames = ActiveSheet
Set wbToAddSheetsTo = ActiveWorkbook
For Each cell In wsWithSheetNames.Range("A2:A5")
With wbToAddSheetsTo
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = cell.Value
If Err.Number = 1004 Then
Debug.Print cell.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next cell
End Sub
Another approach would be to load the cell contents into an array, which might be useful if it was in fact two-dimensional (and if there were tons of names) but it might also be overkill.
另一种方法是将单元格内容加载到一个数组中，如果它实际上是二维的（并且如果有大量名称），这可能很有用，但它也可能是矫枉过正。
回答by Glenn Langford
Thanks Doug, this is great. Slight mod to avoid having to re-name the range:
谢谢道格，这很棒。轻微修改以避免必须重命名范围：
'select list range before running procedure
Sub AddSheets()
Dim cell As Excel.Range
Dim wbToAddSheetsTo As Excel.Workbook
Set wbToAddSheetsTo = ActiveWorkbook
For Each cell In Selection
With wbToAddSheetsTo
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = cell.Value
If Err.Number = 1004 Then
Debug.Print cell.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next cell
End Sub
回答by Lena Skalska
I use this for my needs:
我用它来满足我的需求：
Sub C_CreateEmptySheets()
Dim MyCell As Range, MyRange As Range
'This Macro will create separate tabs based on a list in Distribution Tab A2 down
Set MyRange = Sheets("Distribution").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
回答by Froid Andrewson
with sheets
.add.name="SheetA"
.add.name="SheetB"
.add.name="Sheetc"
end with
