Forum Discussion

Queenie Lai's avatar
Queenie Lai
Copper Contributor
Apr 20, 2020
Solved

Exit Sub not working - VBA

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

 

Macros and VBA
  • JKPieterse's avatar
    JKPieterse
    Apr 21, 2020

    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

3 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Apr 20, 2020

    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
    • Queenie Lai's avatar
      Queenie Lai
      Copper Contributor
      Apr 21, 2020

      JKPieterse 

       

      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

       

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Apr 21, 2020

        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

Resources