Excel添加工作表 [英] Excel Adding Worksheets

问题描述

我需要在名为AllCities的工作表中创建一个基于名称列表创建工作表的子。城市名单从单元格A2开始。工作表需要以列表中的单元格值命名，并且不应该创建任何重复的表单。这是我到目前为止：

I need to create a sub where to create worksheets based off of a list of names in a worksheet named AllCities. The list of city names starts in cell A2. The worksheets need to be named after the cell value in the list, and it should not create any duplicate sheets. This is what I have so far:

Sub addsheets()
Dim myCell As Range
Dim Cities As Range


With Sheets("AllCities")
Set Cities = Sheets("AllCities").Range("A2")
Set Cities = Range(Cities, Cities.End(xlDown))
End With

For Each myCell In Cities
If Not myCell.Value = vbNullString Then
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = myCell.Value
End If
Next myCell

End Sub

推荐答案

看起来，问题在于确保重复项不被创建。我可以想到两种方式来做，但是选择了我认为是最有效的这种情况。

It looks like the question is around ensuring duplicates are not created. I could think of two ways to do this but and have chosen what I believe to be the most efficient for this situation.

1. 记住名字（选择） - 记住可以非常快速检查的字符串中的工作表名称，如果您在数千个选项卡中有大的（25+长度）城市名称，则不会是最佳解决方案，但是在这一点上，我怀疑你会有不同的问题要考虑。


2. 创建一个错误处理proc进行检查 - 您可以调用第二个过程这将检查该表是否存在，这将使处理时间更慢，但如果大规模使用会更安全。

1. Remember the names (Chosen) - Remember the names of the sheets in a string that can be very quickly checked, would not be the best solution if you had large (25+ in length) city names across thousands of tabs, but at that point I suspect you would have different issues to consider.
2. Create an error handling proc that does the check - You could call out to a second procedure that would check if the sheet existed, this would make for a slower processing time but would be safer if used on a large scale.

以下是您的代码，并附带检查重复项。

Below is your code with a check for duplicates included.

Sub addsheets()
Dim myCell      As Range
Dim Cities      As Range
Dim StrSheets   As String
Dim WkSht       As Excel.Worksheet

With ThisWorkbook.Worksheets("AllCities")
    Set Cities = Range(.Range("A2"), .Range("A2").End(xlDown))
End With

StrSheets = "|"
For Each WkSht In ThisWorkbook.Worksheets
    StrSheets = StrSheets & WkSht.Name & "|"
Next

For Each myCell In Cities
    If Not myCell.Value = vbNullString Then
        If InStr(1, StrSheets, "|" & myCell.Value & "|") = 0 Then
            Sheets.Add After:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = myCell.Value
            StrSheets = StrSheets & myCell.Value & "|"
        End If
    End If
Next myCell

End Sub

这篇关于Excel添加工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！