Macro to pick data from folder in desktop and paste in one excel file

[Shakti Bansal]

Hi All,

Can anyone help me in creating a macro

• Which will open a particular folder in desktop
• open each and every excel file one by one
• copy data in range A9 to D (upto last row)
• and paste data
• then open next file and follow above steps

 

[Aravindhan Bharathi]

Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:\NCT DATA")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

Range("AD:D" & Range("A95536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A95536").End(xlUp).Offset(0, 0).PasteSpecial
Application.CutCopyMode = False