Copy data from all the file file from a folder.

[Heera]

Hi,

If suppose I have 10 different files in a folder I want the macro to
open the files one after one and copy the data from the file and paste
it in a different workbook.

While doing the same the macro should also rename the tab with the
files name from which the data is copied.

The number of files may increase or decrease.

I just want to mention the path of the folder I don’t want to copy the
names of the files because every time the file names changes.

Any solution for it.

Regards

Heera

 

[Joel]

I don't know how many sheets from each workbook you need to copy so I'm only
copying the active sheet in each of the opened workbooks. Change "Folder ="
as required.

Sub GetBooks()

Folder = "C:\Temp\"
FName = Dir(Folder & "*.xls")
Do While FName <> ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
With ThisWorkbook
OldBk.ActiveSheet.Copy _
after:=.Sheets(.Sheets.Count)
ActiveSheet.Name = FName
End With
OldBk.Close savechanges:=False
FName = Dir()
Loop

End Sub

 

[Heera]

Hi,

Joel your code is working fine. I would appriciate if you can explain
me what this macro is doing at each step. It will be a great help for
me next time when ever i write the macro.

Regards

Heera

 

[Joel]

Sub GetBooks()

Folder = "C:\Temp\"
'Get each XLS file in the Folder, will return an empty string after last file
FName = Dir(Folder & "*.xls")
'loop until there are no more files to open
Do While FName <> ""
'Open sleected file
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
'open workbook become active so must specify original book
With ThisWorkbook
'copy sheet from open book to this book
'Require AFTER otherwise sheet goes in a new workbook
'Place Copied sheet as last sheet in workbook
OldBk.ActiveSheet.Copy _
after:=.Sheets(.Sheets.Count)
'Make sheet name the same as workbook name
ActiveSheet.Name = FName
End With
'close workbook that was opened.
'specify savechanges false so nothing is changed in opened book
'also savechanges are needed so pop-up window doesn't
'show up after every file.
OldBk.Close savechanges:=False
'get next file in search criteria
FName = Dir()
Loop

End Sub