Apr 19 2020 11:35 PM - edited Apr 20 2020 10:10 PM
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
Apr 20 2020 02:28 AM
@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
Apr 20 2020 10:31 PM
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
Apr 21 2020 01:49 AM
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
Apr 21 2020 01:49 AM
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