Create sheet if it doesn't exist problem

Good evening everyone,

I have a piece of code that check if the sheet exist or not and create it if it doesn't:

Code:

---

For Each cAcc In Range(LastAccount).Cells   
    If Mid(cAcc.Value, 10, 3) = "112" Then
        SheetName = "Conc_" & cAcc.Value
        On Error Resume Next
        Set wsAcc = Sheets(SheetName)
        If Not wsAcc Is Nothing Then
            Worksheets("Conc_" & cAcc.Value).Visible = True
            reclcick = True
        Else
        Sheets("Template_Conc_Bank").Visible = True
        Sheets("Template_Conc_Bank").Copy After:=ActiveWorkbook.Sheets("Template_Conc_Bank")
        Sheets("Template_Conc_Bank (2)").Select
        Sheets("Template_Conc_Bank (2)").Name = "Conc_" & cAcc.Value
        Range("ConcAccountNo") = cAcc.Value
        Range("ConcAccountName") = cAcc.Offset(0, -1).Value
        Sheets("Template_Conc_Bank").Visible = False
        Set wsAcc = Nothing
        End If
    End If
Next cAcc

---

If none of the sheets are created it works really well and create all the sheets corresponding to the condition. But if I add one account to the list that respect the condition "112" then it doesn't create the sheet. the problem seem to be happening in that bit:

Code:

---

Set wsAcc = Sheets(SheetName)
If Not wsAcc Is Nothing Then

---

Even thought I reset the wsAcc it doesn't seem like it reset correctly and detect the new account as thought it already exist even if it doesn't.

Anyone have any clue why? :banghead:

Thank you in advance for the help :)

Code:

---

If Mid(cAcc.Value, 10, 3) = "112" Then
    SheetName = "Conc_" & cAcc.Value

---

What is the value in cAcc?

I see positions 10, 11, and 12 = "112", but depending on the rest of the cell, SheetName might be invalid for some reason

Since you said to ignore errors ...

Code:

---

On Error Resume Next
Set wsAcc = Sheets(SheetName)

---

... it could fail later on


Paul

I would use this.

Code:

---

Function Test_WorkSheetExists()
    MsgBox "WorksheetExists? " & WorkSheetExists("Sheet1"), vbInformation, "ActiveWorkbook.ActiveSheet"
End Function

 'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
    Dim ws As Worksheet, wb As Workbook
    On Error GoTo notExists
    If sWorkbook = "" Then
        Set wb = ActiveWorkbook
        Else
        Set wb = Workbooks(sWorkbook)
    End If
    Set ws = wb.Worksheets(sWorkSheet)
    WorkSheetExists = True
    Exit Function
    notExists:
    WorkSheetExists = False
End Function

---

or

Code:

---

For Each cAcc In Range(LastAccount).Cells
    If Mid(cAcc.Value, 10, 3) = "112" and not evaluate("isref(" & "Conc_" & cAcc.Value & "!A1)") then
        with Sheets("Template_Conc_Bank")
            .visible=true
            .Copy ,Sheets("Template_Conc_Bank")
            .visible=false
        end with
        Sheets(sheets.count).Name = "Conc_" & cAcc.Value
    end if
Next

---

Thank you all for your replies,

I used Kenneth's approach to use a function to return if sheet is working or not and it work like a charm now.

Code:

---

For Each cAcc In Range(LastAccount).Cells   
    If Mid(cAcc.Value, 10, 3) = "112" Then
        SheetName = "Conc_" & cAcc.Value
        ' On Error Resume Next
        ' Set wsAcc = Sheets(SheetName)
        If WorkSheetExists("Conc_" & cAcc.Value) = True Then
            Worksheets("Conc_" & cAcc.Value).Visible = True
            reclcick = True
        Else
            Sheets("Template_Conc_Bank").Visible = True
            Sheets("Template_Conc_Bank").Copy After:=ActiveWorkbook.Sheets("Template_Conc_Bank")
            Sheets("Template_Conc_Bank (2)").Select
            Sheets("Template_Conc_Bank (2)").Name = "Conc_" & cAcc.Value
            Range("ConcAccountNo") = cAcc.Value
            Range("ConcAccountName") = cAcc.Offset(0, -1).Value
            Sheets("Template_Conc_Bank").Visible = False
            Set wsAcc = Nothing
        End If
    End If
Next cAcc

---

As for Paul, cAcc in this range is just a variable to loop in the list Dim cAcc as range and the way I was using my loop/condition the error part was necessary in case the name doesn't exist.
I"m still not sure as to why it failed since I went trough all the list step by step to check (F8) and the condition all seemed to be handled correctly.

snb: I didn't use your code because the emplacement on the new sheet name is almost never equal to the number of sheets in the workbook since a lot of operation deal with creating/deleting ws.

Thank you again :)

Quote:

---

snb: I didn't use your code because the emplacement on the new sheet name is almost never equal to the number of sheets in the workbook since a lot of operation deal with creating/deleting ws.

---

After copying a sheet, the new sheet has always the indexnumber equal to the number of sheets in the workbook !

right, I wasn't thinking straight... not done with my first cofee of the day yet, thought it was the sheet name number at first glance

That's the European advantage ;)