macro to merge excel files in one file

[sreejesh]

Hi,

I need help in merging excel files and creating 1 file. Basically i have multiple excel failes with different names, i end up opening a workbook and add the details from each of the files to the workbook and then run a pivot.

I would be really helpfull if i could just run a macro that would pickup the information from the excel failes from a Folder and add them up in 1 workbook.

Sreejesh

 

[sreejesh]

Hi,

there is one more problem , in all these different files, i only need information from the sheet "data" to be merged into 1 workbook.

Sreejesh

 

[sreejesh]

Hi,

I tried the below code, but it does not seen to copy the last file and its giving me an error msg

Sub MergeSheets()
    Dim SrcBook As Workbook
    Dim fso As Object, f As Object, ff As Object, f1 As Object
       
    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.Getfolder("C:\Documents and Settings\unniks\Desktop\Metricw\")
    Set ff = f.Files
    
    For Each f1 In ff
        Set SrcBook = Workbooks.Open(f1)
        Sheets("Data").Select
        Range("A1:IV" & Range("A65536").End(xlUp).Row).Copy
        ThisWorkbook.Worksheets(1).Activate
        Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
        Application.CutCopyMode = False
        SrcBook.Close
    Next
End Sub]

 

[VoG]

Taka look http://msdn.microsoft.com/en-us/library/cc837974.aspx

 

[sreejesh]

Hi Peter,

That link has too much information which is confusing me, would you able to tell me somthing simple that can do the job.

I just need a code to merge all the files in a folder to one sheet. Only catch is that from all the files in the folder i only need the information from sheets with the name "data".

 

[excelbee2013]

Use this simple macro that open each sheet from all your excel files and merge will columns into one sheet consolidated sheet.

thanks