Hello Excel expert, I found a code below on the internet to copy a list of 100 workbooks in a folder, But this macro didn't work as I want it to be. Basically, it needs to copy data from Sheet "Export Tab" from rows A4:AP down to whatever lines then copy to the "master tracker workbook". Usually, I do this manually which took me the whole day to complete.
Please Help!
Sub GetDataFromFilesInAFolder()
Dim FPath As String
Dim FName As Variant
Dim ws As Worksheet, wsS As Worksheet
Dim wb As Workbook, wbMaster As Workbook
Set wbMaster = ActiveWorkbook
Application.ScreenUpdating = False
FPath = "C:\Users\Acer\Desktop\Tracker\" ' Set your folder path here
FName = Dir(FPath)
While FName <> ""
Set wb = Workbooks.Open(fileName:=FPath & FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
For Each ws In wb.Sheets
If ws.Name = "Export Data" Then
ws.Cells.Copy wbMaster.Sheets(Split(FName, ".")(0)).Range("A1")
End If
Next
'Close wb without saving
wb.Close False
'Set the fileName to the next file
FName = Dir
Wend
End Sub
Please Help!
Sub GetDataFromFilesInAFolder()
Dim FPath As String
Dim FName As Variant
Dim ws As Worksheet, wsS As Worksheet
Dim wb As Workbook, wbMaster As Workbook
Set wbMaster = ActiveWorkbook
Application.ScreenUpdating = False
FPath = "C:\Users\Acer\Desktop\Tracker\" ' Set your folder path here
FName = Dir(FPath)
While FName <> ""
Set wb = Workbooks.Open(fileName:=FPath & FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
For Each ws In wb.Sheets
If ws.Name = "Export Data" Then
ws.Cells.Copy wbMaster.Sheets(Split(FName, ".")(0)).Range("A1")
End If
Next
'Close wb without saving
wb.Close False
'Set the fileName to the next file
FName = Dir
Wend
End Sub