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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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".
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,718
Messages
5,833,289
Members
430,202
Latest member
Faizal5zl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top