Application-defined or object-defined error (1004)

Question

I have a spreadsheet which allows users to enter information onto a single page then press a button to send all the info to where it needs to go. I have a function which checks to see if a sheet exists and then creates a copy of a template sheet and renames it to the required name.

    Application.ScreenUpdating = False
    For i = 1 To 10
        OpName = Cells(i + 3, 2).Value
        If Not OpName = "" Then
            OpCheck (OpName)

So the code above cycles through the cells down row B and runs the opcheck function as described above.

Function OpCheck(Init As String)
    Init = UCase(Init)
    exists = False
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = Init Then
            exists = True
        End If
    Next i
    If Not exists Then
        Sheets("Op Template").Visible = True
        Sheets("Op Template").Activate
        ActiveSheet.Copy After:=Worksheets(Sheets.Count)
        ActiveSheet.Name = Init
        RenameTable (Init)

        On Error Resume Next

    End If
    Sheets("Op Template").Visible = False

End Function

This is the code for the function and the error occurs in the line

ActiveSheet.Name = Init

This has worked some of the time but is now failing to find a sheet with the name TS and gets an error when it tries to rename a new page with a name that already exists. I just don't understand why it isn't finding the page in the first place.

Thanks in advance.

You really should qualify the workbook the worksheets are in. — BigBen

xidgel xidgel · Accepted Answer · 2020-02-05T16:50:13

The reason your code is sometimes failing to find is because you need to exit the For loop once the sheet is found. A fix is:

For i = 1 To Worksheets.Count
    If Worksheets(i).Name = Init Then
        exists = True
        Exit For    ' ADD THIS LINE
    End If
Next i

A couple of other code comments:

Consider changing Function OpCheck to Sub OpCheck since you're not returning anything
Your code may be causing Init to be converted to upper case in the function or sub that calls OpCheck. To avoid this, change the declaration to: OpCheck(ByVal Init As String)

This section of code:

Sheets("Op Template").Visible = True
Sheets("Op Template").Activate
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = Init
RenameTable (Init)

can be made less risky by avoiding ActiveSheet

dim ws as Worksheet
Set ws = Sheets("Op Template")
ws.Visible = True
ws.Copy After:=Worksheets(Sheets.Count)

' Instead of this
' ActiveSheet.Name = Init
' Something like this
Worksheets(Sheets.Count).Name = Init
RenameTable (Init)

Hope that helps

Application-defined or object-defined error (1004)

1 Answers