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