Hi All,
I have attached four workbooks. 3 from suppliers and one masterdata.
The three supplier’ workbooks are Supplier-a.xlsx, Supplier-b.xlsx, Supplier-c.xlsx and the master zMaster.xlsm
I am able to transfer the data from the suppliers’ workbooks to the master workbook, but, I have to hard code the number of lines.
I want to alter the code below to do two things
1. Be able to drag thousands of lines.
2. Update the lines that have been dragged to show “Yes” in column E and they are not dragged again.
Regards
Raghu
Code
Sub LoopThroughDirectory()
Dim MyFile As String
Dim eRow As Long
Dim LastRow As Long
Dim i As Long
Dim FilePath As String
FilePath = ActiveWorkbook.Path & "\"
Debug.Print FilePath
MyFile = Dir(FilePath)
Debug.Print MyFile
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If
Workbooks.Open (FilePath & MyFile)
Range("A2:D20").Copy
ActiveWorkbook.Close
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(eRow, 1), Cells(eRow, 5))
MyFile = Dir
ActiveWorkbook.Save
Loop
End Sub
[COLOR=#444444][FONT=Lato][/FONT][/COLOR]
Display More