Macro to combine many workbooks into one

[Dropzone01]

Hello,

I have a number of workbooks (over 300) each with one sheet used as a daily total tabulation. I would like to combine all the workbooks into a single Master workbook where each is now a seperate worksheet. I have a Macro written that I feel should do what I am looking for but when it runs it loops 3 times and then comes up with a Run-time error '1004' "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic."

It renames the first sheet to the name of the file "Daily Report", the second to "Daily Report (2)" and the third to "Daily Report ( (3" and then runs into the error. I don't understand the reason for the last one to become "( (3)" and not "(3)"

Here is the code I am trying to use:

Sub GetSheets()
Path = "y:\DailyTotals\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

The debug indicates the line error is:

Sheet.Copy After:=ThisWorkbook.Sheets(1)

Thanks for any help you can give,

DZ

[jolivanes]

Hi DZ
First of all, you need your code between code tags. At the top, click on Forum Rules and read #3.

For your request, do you want to copy one particular sheet, the sheet with the tabulation, only?
If so, what is the name of that sheet? Are all these tabulation sheets named the same in all the workbooks?
Or do you want to copy every sheet of every workbook?
From your code it looks like you want to copy every sheet from every workbook, You might end up with 900+ sheets in your Master.

Regards

John

[jolivanes]

If you want every sheet of every workbook you could try this.
Save your "Master" Workbook in the Folder where all the other Workbooks reside and then run this code.

Please Login or Register  to view this content.