Exit Sub not working - VBA

[Queenie Lai]

I am looking to loop through the names of all sheets. If the name"Apr2020" exists then Exit Sub. If it doesn't exist then copy the last sheet after the last tab.

The code below does not work and could anyone help to see what went wrong?

Also for tabname.Name = "Apr2020" , now I have to change the name in the code every month. How can I code it so it reflects the current month and year and I don't need to change the name every month? 

Sub copy()

Application.ScreenUpdating = False

Set closedBook = Workbooks.Open("C:\file123.xlsx")


Dim tabname As Name
For Each tabname In closedBook.Names
If tabname.Name = "Apr2020" Then
Exit Sub
Else
closedBook.Sheets(Sheets.Count).copy After:=closedBook.Sheets(Sheets.Count)
End If
Next

closedBook.Close SaveChanges:=True



Application.ScreenUpdating = True
End Sub

[#2]

@Queenie Lai I think you need this:

Sub copy()
    Dim tabname As Name
    Application.ScreenUpdating = False
    Set closedBook = Workbooks.Open("C:\file123.xlsx")
    For Each tabname In closedBook.Names
        If tabname.Name = "Apr2020" Then
            Exit Sub
        End If
    Next
    closedBook.Sheets(Sheets.Count).copy After:=closedBook.Sheets(Sheets.Count)
    closedBook.Close SaveChanges:=True
    Application.ScreenUpdating = True
End Sub

[#3]

@Jan Karel Pieterse 

I have another set of code from another forum. The code stops at 

ActiveSheet.Name = strMonth

It opened the Excel file and copied the last sheet and named Apr2020 (2). Would you be able to look at this for me and fix the code?

Sub copy()
    Dim wbkClosed As Workbook
    Dim wksCurrent As Worksheet
    Dim bolExists As Boolean
    Dim strMonth As String

    Application.ScreenUpdating = False
    Set closedBook = Workbooks.Open("C:\File123.xlsx")
    strMonth = Format(Now, "Mmmyyyy")
    bolExists = False
    For Each tabname In closedBook.Names
        If (closedBook.Name = strMonth) Then
            bolExists = True
            Exit For
        End If
    Next
    With wbkClosed
        If bolExists Then
            .Close SaveChanges:=False
            MsgBox "Worksheet '" & strMonth & "' not added (already exists)", vbOKOnly + vbInformation, "Copy"
        Else
            closedBook.Sheets(Sheets.Count).copy After:=closedBook.Sheets(Sheets.Count)

            ActiveSheet.Name = strMonth
            closedBook.Close SaveChanges:=True
            
            MsgBox "Worksheet '" & strMonth & "' added", vbOKOnly + vbInformation, "Copy"
        End If
    End With
    Set wksCurrent = Nothing
    Set wbkClosed = Nothing
    Application.ScreenUpdating = True
End Sub

[#4 · Accepted solution]

@Queenie Lai I am wondering whether the loop is correct. You are looping through the name collection of the workbook object. I *think* you intended to loop through the worksheet collection:

Sub copy()
    Dim tabname As Worksheet
    Application.ScreenUpdating = False
    Set closedBook = Workbooks.Open("C:\file123.xlsx")
    For Each tabname In closedBook.Worksheets
        If tabname.Name = "Apr2020" Then
            Exit Sub
        End If
    Next
    closedBook.Sheets(Sheets.Count).copy After:=closedBook.Sheets(Sheets.Count)
    closedBook.Close SaveChanges:=True
    Application.ScreenUpdating = True
End Sub