excelbytes
Board Regular
- Joined
- Dec 11, 2014
- Messages
- 249
- Office Version
- 365
- Platform
- Windows
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 Help\Edna\Inversion 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
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 Help\Edna\Inversion 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