macro to merge excel files in one file

sreejesh

New Member
Joined
Oct 3, 2009
Messages
49
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

sreejesh

New Member
Joined
Oct 3, 2009
Messages
49
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

New Member
Joined
Oct 3, 2009
Messages
49
Hi,

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

HTML:
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]
 

sreejesh

New Member
Joined
Oct 3, 2009
Messages
49
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".
 

Watch MrExcel Video

Forum statistics

Threads
1,108,457
Messages
5,523,078
Members
409,497
Latest member
SFCSteve

This Week's Hot Topics

Top