Macro Multiple excel files into one workbook as separate sheets

Hi,

I'm looking for a macro that can consolidate multiple files in a folder to one workbook as separate sheets. I have a found a few hints on how to merge all the info to one sheet but I need to keep it separate. Its would need to follow this basic path.

- Take files from single folder
- Open all of then in one workbook as separate sheets
- Rename each sheet to the original file name.

The information I need to consolidate is a daily report so the macro would need to be able to work new files being added daily.


Would really appreciate any help on this.

something along the lines of:

Please Login or Register  to view this content.

changing E:\folder\ to whatever the folder where your files are stored is.

Thanks for this,

I've changed the E:\folder\ but it only seems to be opening one of the files from the folder rather than opening all the files into the totals workbook as sheets. Is it something to do with the file address?

Can you post what you changed the code to please. I tested it on some dummy files and it worked ok.

Sub macro_1()
Dim fname, wb1
Set wb1 = ActiveWorkbook
fname = Dir("U:\Data Role\SAR Reports\*.xl*")
Do Until fname = ""
With Workbooks.Open("U:\Data Role\SAR Reports\" & fname)
.Sheets(1).Copy wb1.Sheets(1)
ActiveSheet.Name = .Name
.Close
End With
fname = Dir()
Loop
End Sub

Looks ok, you could try:

Please Login or Register  to view this content.

but I'm not sure that would work. What type of files are in that folder?

Just xls. files

Afraid I can't really replicate the problem then, you could try this instead to see if it works any better:

Please Login or Register  to view this content.

Thanks again for your help i can use a couple of these bits but am going to keep trying to get this to work