Error 1004 renaming worksheets

Question

I keep having troubles with "Error 1004 - Method 'Name' of object '_Worksheet'" in my VBA macro. Basically the idea of this macro is to check the cells of a column (containing strings) and to compare them to the names of the worksheets of my workbook. If there is a cell whose value doesn't appear among the names of the worksheets, the macro should create a new worksheet and rename it after the value inside the cell.

    For j = (RowStart + 1) To 500

    'allocate the value of the reference cell in cell_content
    cell_content = Worksheets("Worksheet1").Cells(j, ColStart).Value
    Switch = 0    

    'check if cell_content appears as the name of one of the worksheet
    For i = 1 To Sheets.Count
        If cell_content = Sheets(i).Name Then
            Switch = Switch + 1
        End If
    Next i

    'if it does not, the macro creates a new worksheets with the name of cell_content
    If Switch = 0 Then
        Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            ws.Name = cell_content
    End If        
Next j

Consider that ColStart and RowStart are just the reference values for the column under consideration and the variable Switch is used to define whether or not it is required to create a new worksheet.

The problem is that the very last part of the macro keeps giving me error 1004. Does anyone know what can be the problem?

Thank you very much

Vityata Vityata · Accepted Answer · 2018-03-13T09:46:13

I guess your workbook is locked. A small code like this works on an empty Excel:

Public Sub TestMe()    
    Dim ws As Worksheet
    Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    ws.Name = "CodeThatWorks"    
End Sub

What may be the other possible reason that your code does not work is that the name is like this ws.Name = "BadNamesIncludeThese:]\?*[]", thus including special characters which are not allowed for a name in the worksheet.

Error 1004 renaming worksheets

1 Answers