Results 1 to 8 of 8

Thread: Create sheet if it doesn't exist problem

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Jul 2013
    Posts
    50
    Location

    Question 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:

    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
    Last edited by Aussiebear; 03-09-2025 at 03:50 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •