Some background. This file is being used by someone in Mexico, so I don't know if that has anything to do with the error she's receiving. I wrote this using Office 365. It works perfectly on my computer and another computer in my house using Office 365. I tried on Excel for Mac computer and it worked fine. I tried it on a friend's computer using Excel 2013 and I get the same error. The error says:
"An error has been produced 1004 in running time
This name is already used. Try with a different one"
When I click on debug, it highlights the line of code that is in bold and red below
I made sure the path was correct for any computer we tried it on. The error occurs right after it's copying the last of the 4 files.
What the code does is copies the worksheets from the other four files in the folder to the file called "BD - Inversion Comunitaria EDR", then adds two new worksheets at the end. It never gets to the point of adding the new worksheets. An image of the folder containing all 5 files is attached.
I have the following VBA code:
Sub ICEDR()
Dim Path As String
Path = "F:Excel HelpEdnaInversion Comunitaria EDR"
Dim FileName As String
FileName = Dir(Path & "*.xlsx")
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Do While FileName <> ""
Workbooks.Open Path & FileName
For Each ws In ActiveWorkbook.Sheets
ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Next ws
Workbooks(FileName).Close
FileName = Dir()
Loop
Worksheets(1).Delete
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Peticiones"
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Proyectos"
End Sub
Hi Michael,
The error means that you are trying to copy a worksheet that already exists in the destination.
You cannot copy Sheet1 from Book1 into Book2, if Book2 already has a sheet named Sheet1.
To avoid the error, check if the sheet you want to copy already exists in destination workbook:
Function SheetExists(Byval ShName as string, Wb as workbook) as boolean
On Error Resume Next
SheetExists=Not wb.Worksheets(ShName) is Nothing
End Function
Use it like this:
If SheetExists(ws.Name,ThisWorkbook)=False then
ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Else
Debug.Print "Sheet name exists: " & ws.name
End If
As it turns out, there were hidden worksheets in each workbook that did have the same name. Deleting or changing the names of those solved the issue. However, I noticed that with Office 365, Excel would automatically rename those worksheets with a parenthetical number after each and not result in the error. Is this a new feature in Office 365, or what version did this option appear in?
That happens in all versions, if you copy the sheets MANUALLY. Copying sheets in VBA is another story, the usual user interface automation is not functional because ... there is no user action when vba code is running.
