I have tried a few VBA codes to either combine 12 workbooks into one (all with 6 worksheets so new workbook would have 76 worksheets) or also to create 6 new workbooks all with the same sheet from the 12 workbooks (all 12 x Sheet1 in one workbook, all 12 x Sheet2 in another). I would prefer the first option. I have tried the below option and had limited success. I get a run-time error 1004 'That name is already taken. Try a different one'.
All worksheets within the individual workbooks have the same name.
Using this code, seems to let me combine 3 workbooks before the error appears
- Sub CombineFiles()
- Dim Path As String
- Dim FileName As String
- Dim Wkb As Workbook
- Dim WS As Worksheet
- Application.EnableEvents = False
- Application.ScreenUpdating = False
- Path = "O:\Nth Adelaide Manager\Wage Reports\Test"
- FileName = Dir(Path & "\*.xls", vbNormal)
- Do Until FileName = ""
- Set Wkb = Workbooks.Open(FileName:=Path & "" & FileName)
- For Each WS In Wkb.Worksheets
- WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
- Next WS
- Wkb.Close False
- FileName = Dir()
- Application.EnableEvents = True
- Application.ScreenUpdating = True
- End Sub
Moderator Comment: Please read the rules you agreed to when you joined this forum. Pay particular attention to the rule on Code Tags. I have added them for you today. Please comply in the future.