Create sheet if it doesn't exist problem

[Jomathr]

Good evening everyone,

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

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:

        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?

Thank you in advance for the help

[Paul_Hossler]

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 ...

On Error Resume Next
Set wsAcc = Sheets(SheetName)

... it could fail later on


Paul

[Kenneth Hobs]

I would use this.

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

[snb]

or

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

[Jomathr]

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.

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

[snb]

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 !

[Jomathr]

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

[snb]

That's the European advantage