VBA 按钮从模板创建新工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42654686/
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 button create new sheet from template
提问by David Kris
I'm new to Excel so I hope this makes sense. In the code below, I have a user form that contains a button, and once clicked it creates a new sheet and renames the sheet corresponding to the text fields that were filled out on the form. I have a template sheet in my workbook and I was wondering if there are any ways to have the newly created sheets from the user form to follow the same template. I also understand that this is the code to create an exact replica of a sheet:
我是 Excel 新手，所以我希望这是有道理的。在下面的代码中，我有一个包含按钮的用户表单，单击它后会创建一个新工作表并重命名与表单上填写的文本字段相对应的工作表。我的工作簿中有一个模板工作表，我想知道是否有任何方法可以让用户表单中新创建的工作表遵循相同的模板。我也明白这是创建工作表的精确副本的代码：
Dim i as byte
for i=1 to 5
Sheets("TEMPLATE").Copy after:=sheets("TEMPLATE")
Next I
But I don't know how or if its possible to fit it in with my code:
但我不知道如何或是否可以将它与我的代码相适应：
If Me.cbStores.Value = "Northern / Northmart" Then
Dim sh As Worksheet
Set sh = Sheets.Add
sh.Name = AddEmployeeUF.txtFirstname.Text + AddEmployeeUF.txtMiddleinitial.Text + AddEmployeeUF.txtLastname.Text + "Template"
ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), Address:="", SubAddress:=sh.Name & "!A1", TextToDisplay:="View"
End If
Anything helps! Thanks.
什么都有帮助！谢谢。
回答by FunThomas
The Sheets.Copy method unfortunately doesn't return a reference to the new created sheet. However, you can access it easily as activeSheet.
不幸的是， Sheets.Copy 方法不会返回对新创建的工作表的引用。但是，您可以通过 activeSheet 轻松访问它。
Dim i as byte, sh as worksheet
for i=1 to 5
Sheets("TEMPLATE").Copy after:=sheets("TEMPLATE")
set sh = activeSheet
' Do whatever you have to do with the new sheet
sh.Name = AddEmployeeUF.txtFirstname.Text + AddEmployeeUF.txtMiddleinitial.Text + AddEmployeeUF.txtLastname.Text + "Template"
ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), Address:="", SubAddress:=sh.Name & "!A1", TextToDisplay:="View"
Next I
