Excel VBA 创建按钮以复制/重命名工作表和整个工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20693769/
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
Excel VBA Create buttons to copy/rename sheets and whole workbook
提问by Matt
I am trying to create a roadway design spreadsheet that can be used over and over again for hundreds of alignments. I got the everything working with a master sheet to be copied and two sheets used as lookup tables. I want to add two buttons in the corner that can be used to:
我正在尝试创建一个道路设计电子表格，可以反复用于数百个路线。我得到了与要复制的主表和用作查找表的两张表一起工作的所有内容。我想在角落添加两个按钮，可用于：
1) Copy the Master Sheet and rename it the alignmentname-##. This will be within the current workbook and will be used for each curve in the roadway alignment. It would be even better if there was a way to delete out these two buttons in the copied sheets.
1) 复制主表并将其重命名为alignmentname-##。这将在当前工作簿中，并将用于道路路线中的每条曲线。如果有办法删除复制的工作表中的这两个按钮，那就更好了。
2) A button to copy just the Master sheet, and two supplement sheets to a new workbook.
2) 将主表和两个补充表复制到新工作簿的按钮。
So far I have:
到目前为止，我有：
Sub Button10_Click()
Worksheets("Master (DO NOT MODIFY)").Copy _
Before:=ActiveWorkbook.Sheets("Master (DO NOT MODIFY)")
End Sub
It works fine for now just creating a copy of the base file, but I have not been able to rename it. The code for renaming the sheet is not working and I am not quite sure why.
现在只需创建基本文件的副本就可以正常工作，但我无法重命名它。重命名工作表的代码不起作用，我不太确定为什么。
Sheets(Count).Name = Range("H7").Value & "-" & Count
Where count is a public variable that goes up by one everytime a new curve is addedand H7 is the name of the alignment.
其中 count 是一个公共变量，每次添加新曲线时都会增加一，H7 是对齐的名称。
I have also played with the ActiveSheet. activesheetand Worksheets
我也玩过 ActiveSheet。activesheet和Worksheets
Code for the first Button:
第一个按钮的代码：
Public Count As Integer
Sub Button10_Click()
If Count = 0 Then
Count = 1
End If
Dim ws As Worksheet
Worksheets("Master (DO NOT MODIFY)").Copy _
Before:=ActiveWorkbook.Sheets("Master (DO NOT MODIFY)")
Set ws = ActiveSheet
ws.Name = Range("C2").Value & Count
Count = Count + 1
End Sub
回答by Siddharth Rout
Is this what you are trying? You cannot use countas the sheet is moved beforethe sheet and if you are incrementing the count then it will refer to the wrong sheet.
这是你正在尝试的吗？您不能count在工作表移动时before使用工作表，如果您增加计数，则它将引用错误的工作表。
Sub Button10_Click()
Dim ws As Worksheet
Worksheets("Master (DO NOT MODIFY)").Copy _
Before:=ActiveWorkbook.Sheets("Master (DO NOT MODIFY)")
Set ws = ActiveSheet
ws.Name = Range("H7").Value & "-" & Count
End Sub
Regarding your 2nd question, you can try it like this. I am assuming that the master is not the last or the 2nd last sheet. Also there are two more sheets after the master.
关于你的第二个问题，你可以这样试试。我假设主人不是最后一张或倒数第二张。大师之后还有两张纸。
Sub Button10_Click()
Dim ws As Worksheet
Dim wsMaster As Worksheet
Dim MyArray(1 To 3) As String
Dim n As Long
Set wsMaster = ThisWorkbook.Worksheets("Master (DO NOT MODIFY)")
wsMaster.Copy Before:=wsMaster
Set ws = ActiveSheet
ws.Name = Range("H7").Value & "-" & Count
n = ThisWorkbook.Sheets.Count
MyArray(1) = wsMaster.Name
MyArray(2) = ThisWorkbook.Sheets(n - 1).Name
MyArray(3) = ThisWorkbook.Sheets(n).Name
'~~> This will create a new workbook with the 3 sheets
ThisWorkbook.Sheets(MyArray).Copy
End Sub
