eugene_9787
New Member
- Joined
- Apr 20, 2020
- Messages
- 7
- Office Version
- 2013
- 2010
- Platform
- Windows
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
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