vba 尝试在 xls 工作表上设置名称时出现 1004 错误

1004 error when trying to set name on xls worksheet

提问by agagelis

I'm trying to create worksheets with name taken from sheet1cells, but I'm always getting a 1004 errorwhen executing Worksheets(Worksheets.Count).name = companyName

我正在尝试创建名称取自sheet1单元格的工作表，但1004 error在执行时 总是得到一个Worksheets(Worksheets.Count).name = companyName

I tried setting the sheets name by PrefferedNameand worked... and I also checked with a MsgBoxthat companyNamevariable has the last sheet's name...

我尝试设置PrefferedName工作表名称并工作......我还检查了MsgBox该companyName变量具有最后一张工作表的名称......

Sub Find2()
    Dim i, k As Integer
    Dim j, l As Integer
    Dim Counter As Integer
    Dim dateAnnounced As Date
    Dim fromDate As Date
    Dim currentCellDate As Date
    Dim daysBefore As Integer
    Dim kk As Integer
    Dim from1 As Integer
    Dim companyName As Variant

    Set originsheet = ThisWorkbook.Worksheets("Sheet1")

    daysBefore = 30

    i = 3
    j = 4
    Counter = 0
    k = 5
    l = 4179
    dateAnnounced = Cells(i, j).Value

    For Each cel In Range(Cells(1, k), Cells(1, 4179))
        currentCellDate = cel.Value

        If currentCellDate = dateAnnounced Then
             MsgBox k
            Exit For
        End If

        k = k + 1
    Next cel

    kk = k
    from1 = k - daysBefore

    ThisWorkbook.Sheets.Add after:=Sheets(Worksheets.Count)
    companyName = Worksheets("Sheet1").Cells(i, j - 1).Value
    Worksheets(Worksheets.Count).name = companyName

    MsgBox name

    For Each cel In Range(Cells(1, from1), Cells(1, kk))
        If from1 = kk Then
             MsgBox cel.Value
            Exit For
        Else
            Counter = Counter + 1
        End If

        from1 = from1 - 1
    Next cel

    MsgBox Counter

End Sub

采纳答案by Brian Pressler

If you run this code more than once, you will get the 1004 run-time error because it will try to add a sheet with the same name. All sheets must have a unique name. You might need to add a check to ensure that a worksheet with the name does not already exist before creating a new sheet. Something like:

如果您多次运行此代码，您将收到 1004 运行时错误，因为它会尝试添加具有相同名称的工作表。所有工作表都必须有一个唯一的名称。您可能需要添加检查以确保在创建新工作表之前不存在具有该名称的工作表。就像是：

companyName = Worksheets("Sheet1").Cells(i, j - 1).Value
WorksheetExists = False
For Each Sht In ThisWorkbook.Worksheets
    If UCase(Sht.Name) = UCase(companyName) Then
        WorksheetExists = True
        Exit For
    End If
Next Sht
If WorksheetExists Then Exit Sub

ThisWorkbook.Sheets.Add after:=Sheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = companyName

回答by Sean

In addition to a worksheet name that is not unique, you can also get error 1004 if the worksheet name is more than 31 characters long.
Try doing:
Worksheets(Worksheets.Count).name = Left(companyName, 31)

除了不唯一的工作表名称之外，如果工作表名称的长度超过 31 个字符，您还会收到错误 1004。
尝试做：
Worksheets(Worksheets.Count).name = Left(companyName, 31)