Exit Sub not working

[eugene_9787]

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? Could anyone please help me?

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

 

[Michael M]

Try using...UNTESTED

Sub copy()
Dim ws As Worksheet
Application.ScreenUpdating = False
Set closedBook = Workbooks.Open("C:\file123.xlsx")
For Each ws In closedBook
If ws.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

 

[CephasOz]

Give the following code a run (also untested). As always, run the code against a copy of your workbook.

Sub copy()
    Dim wbkClosed As Workbook
    Dim wksCurrent As Worksheet
    Dim bolExists As Boolean
    Dim strMonth As String
    '
    Application.ScreenUpdating = False
    Set wbkClosed = Workbooks.Open("C:\file123.xlsx")
    strMonth = Format(Now, "Mmmyyyy")
    bolExists = False
    For Each wksCurrent In wbkClosed
        If (wksCurrent.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
            .Worksheets(.Worksheets.Count).copy After:=.Worksheets(.Worksheets.Count)
            .Worksheets(.Worksheets.Count).Name = strMonth
            .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

 

[eugene_9787]

Try using...UNTESTED

Sub copy()
Dim ws As Worksheet
Application.ScreenUpdating = False
Set closedBook = Workbooks.Open("C:\file123.xlsx")
For Each ws In closedBook
If ws.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

Hi Michael,

Thank you for helping me. I ran the code and it stops at

For Each ws In closedBook

There is an error message: Run-time error '438': Object doesn't support this property or method.

 

[Michael M]

Ok, try it this way.....but I'm pretty sure yout Fpath isn't the Root directory, maybe something like "C:\Data" or similar !!

Sub copy()
Dim ws As Worksheet, wb As Workbook, fname As String, fpath As String
Application.ScreenUpdating = False
fpath = "C:\"
fname = fpath & "file123.xlsx"
Set wb = Workbooks.Open(fname)
Set wb = ActiveWorkbook
For Each ws In wb
If ws.Name = "Apr2020" Then
Exit Sub
Else
wb.Sheets(Sheets.Count).copy After:=wb.Sheets(Sheets.Count)
End If
Next
wb.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

 

[eugene_9787]

Give the following code a run (also untested). As always, run the code against a copy of your workbook.

Sub copy()
    Dim wbkClosed As Workbook
    Dim wksCurrent As Worksheet
    Dim bolExists As Boolean
    Dim strMonth As String
    '
    Application.ScreenUpdating = False
    Set wbkClosed = Workbooks.Open("C:\file123.xlsx")
    strMonth = Format(Now, "Mmmyyyy")
    bolExists = False
    For Each wksCurrent In wbkClosed
        If (wksCurrent.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
            .Worksheets(.Worksheets.Count).copy After:=.Worksheets(.Worksheets.Count)
            .Worksheets(.Worksheets.Count).Name = strMonth
            .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

Thank you for helping me! Your code is very well laid out. I tried running the code and it stops at

For Each wksCurrent In wbkClosed

There is an error message: Run-time error '424': Object required.

Would you be able to help me fix this so I can run the code again? Thank you.

 

[eugene_9787]

Ok, try it this way.....but I'm pretty sure yout Fpath isn't the Root directory, maybe something like "C:\Data" or similar !!

Sub copy()
Dim ws As Worksheet, wb As Workbook, fname As String, fpath As String
Application.ScreenUpdating = False
fpath = "C:\"
fname = fpath & "file123.xlsx"
Set wb = Workbooks.Open(fname)
Set wb = ActiveWorkbook
For Each ws In wb
If ws.Name = "Apr2020" Then
Exit Sub
Else
wb.Sheets(Sheets.Count).copy After:=wb.Sheets(Sheets.Count)
End If
Next
wb.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

Hi Michael,

Thank you for your help.

The filepath is just a make-up one. The real one I am using works well.

 

[eugene_9787]

Thank you for helping me! Your code is very well laid out. I tried running the code and it stops at

For Each wksCurrent In wbkClosed

There is an error message: Run-time error '424': Object required.

Would you be able to help me fix this so I can run the code again? Thank you.

I fixed the code in the For loop.

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:\Users\queen\Dropbox (Ohmproperty)\3. Property Management\PM Administrative\BURNABY\202 5883 Barker Ave\2 Landlord Reporting\Monthly Statements & Budgets\202 5883 Barker Ave Financials.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

It goes until

ActiveSheet.Name = strMonth

And there is an error: That name is already taken. Try a different one. And the Excel file is opened with a new sheet named "Apr2020 (2).

 

[eugene_9787]

I fixed the code in the For loop.

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:\Users\queen\Dropbox (Ohmproperty)\3. Property Management\PM Administrative\BURNABY\202 5883 Barker Ave\2 Landlord Reporting\Monthly Statements & Budgets\202 5883 Barker Ave Financials.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

It goes until

ActiveSheet.Name = strMonth

And there is an error: That name is already taken. Try a different one. And the Excel file is opened with a new sheet named "Apr2020 (2).

Oh I accidentally posted my filepath

 

[Michael M]

It doesn't matter, it's on your C:\Drive, so nobody can access it but you...