vba 添加工作表时更新 sheet.count 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21417850/
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
Updating sheets.count value when adding worksheets
提问by nickalbe
I am writing a macro to copy a sheet and add it to a specific location within the workbook. This macro will be used for workbooks with different numbers of sheets, so I want it to continue to copy and add worksheets until there are no more sheets left.
我正在编写一个宏来复制工作表并将其添加到工作簿中的特定位置。此宏将用于具有不同工作表数量的工作簿，因此我希望它继续复制和添加工作表，直到没有更多工作表为止。
Sub Macro()
Dim x As Integer
For x = 3 To Sheets.Count Step 3
Sheets(x).Select
Sheets(x).Copy Before:=Sheets(x + 3)
A bunch more code...
Next
The macro obviously runs from 3 to the total number of worksheets, but because the total number of worksheets changes after each step (due to copying/adding a worksheet), the macro stops before it gets to the end of the workbook (I'm guessing because it stops running when the counter reaches the start value of sheets.count). Any suggestions on how to make the macro continue through to the end? This is my first experience writing code so please be gentle :)
宏显然从 3 到工作表总数运行，但由于工作表总数在每一步后发生变化（由于复制/添加工作表），宏在到达工作簿末尾之前停止（我是猜测是因为当计数器达到 sheet.count 的起始值时它停止运行）。关于如何使宏持续到最后的任何建议？这是我第一次写代码的经验，所以请温柔:)
回答by Jerome Montino
Let's assume you want to add xsheets so you can reach sheet count y. However, you want all these sheets to be created before the last sheet in your workbook called End. One approach is below:
假设您要添加工作x表，以便达到工作表计数y。但是，您希望在工作簿中名为 的最后一个工作表之前创建所有这些工作表End。一种方法如下：
Sub AddMoreSheets()
ShCount = ThisWorkbook.Sheets.Count
TargetCount = 7
SheetsToAdd = TargetCount - ShCount
Do Until SheetsToAdd = 0
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Sheets("End")
SheetsToAdd = SheetsToAdd - 1
Loop
End Sub
But what if you're not always sure if the name of your last sheet is End? One approach is the following:
但是，如果您并不总是确定最后一张纸的名称是否为End呢？一种方法如下：
Sub AddMoreSheets2()
ShCount = ThisWorkbook.Sheets.Count
TargetCount = 7
SheetsToAdd = TargetCount - ShCount
Do Until SheetsToAdd = 0
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Sheets(Sheets.Count)
SheetsToAdd = SheetsToAdd - 1
Loop
End Sub
However, for some reason, you really want to use a For Loopfor this. Here's the catch: you want to name them based on a list in a sheet called Start! Here's one way to do it:
但是，出于某种原因，您确实想为此使用 a For Loop。这里有一个问题：您想根据名为Start! 这是一种方法：
Sub AddMoreSheets3()
Dim ListOfNames As Variant, ShName As Variant
ListOfNames = Sheets("Start").Range("A1:A5").Value 'Modify accordingly.
For Each ShName In ListOfNames
Set NewSht = ThisWorkbook.Sheets.Add(Before:=Sheets(Sheets.Count))
NewSht.Name = ShName
Next ShName
End Sub
Hope this helps. ;)
希望这可以帮助。;)
