vba 基于主工作表创建工作表，然后重命名每个选项卡
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16549900/
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
Create worksheets based on a master worksheet and then rename each tab
提问by user2382884
I want to create worksheets from a list of "projects" from a master worksheet. When a worksheet is created I want it to be renamed as the project number from the list.
我想从主工作表的“项目”列表中创建工作表。创建工作表时，我希望将其重命名为列表中的项目编号。
I found two macros that do the job but I need them to work together.
我找到了两个可以完成这项工作的宏，但我需要它们一起工作。
This one creates tabs and renames them.
这个创建选项卡并重命名它们。
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Invoice Summary").Range("B11")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
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
End Sub
This one copies master tab and creates another worksheet.
这个复制主选项卡并创建另一个工作表。
Sub Test()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
End Sub
回答by mr.Reband
Assuming there is no worksheet named "Master (2)" in your workbook, this code should work:
假设您的工作簿中没有名为“Master (2)”的工作表，则此代码应该可以工作：
Sub CreateSheetsFromAList()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Invoice Summary").Range("B11")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
ThisWorkbook.Worksheets("Master (2)").Name = MyCell.Value
Next MyCell
End Sub
It does seem odd that there is no worksheet object returned from the Worksheet.Copy method.
没有从 Worksheet.Copy 方法返回的工作表对象似乎很奇怪。
One thing to note when using xlDown to get the end range -- if you have zero cells or one cell in your range, xlDown will extend all the way to the last row of the worksheet, which will produce undesirable behavior. In this case, you'll get an error when attempting to rename the target sheet, but it is something to look out for.
使用 xlDown 获取结束范围时要注意的一件事——如果您的范围中有零个单元格或一个单元格，xlDown 将一直延伸到工作表的最后一行，这将产生不良行为。在这种情况下，尝试重命名目标工作表时会出现错误，但需要注意这一点。
