Copy data from Unique files into Masterfile all the files in the same folder.

**DocAElstein** · 03-23-2018, 02:21 PM

Hi Raghavendra

I think this code will do some of what you want

Code:

Sub Raghavendra2() ' http://www.excelfox.com/forum/showthread.php/2238-Copy-data-from-Unique-files-into-Masterfile-all-the-files-in-the-same-folder
Dim LisWb As Workbook
 Set LisWb = ThisWorkbook
Dim Ws2 As Worksheet, Ws1 As Worksheet
 Set Ws2 = LisWb.Worksheets.Item(2): Set Ws1 = LisWb.Worksheets.Item(1):
Dim strWb As String: Let strWb = Dir(ThisWorkbook.Path & "\" & "*" & ".xlsx", vbNormal)
   Do '    Loop  through all .xlsx Files in same Folder as this workbook
    Let Ws2.Range("A2:A1000").Value = "=" & "'" & ThisWorkbook.Path & "\[" & strWb & "]Sheet1'!$A2"
   Dim Lr As Long
    Let Lr = Ws2.Range("A2:A1000").Find(what:=0, after:=Ws2.Range("A2"), LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext).Row - 1
'    Let Ws2.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").Value = "=" & "'" & ThisWorkbook.Path & "\[" & strWb & "]Sheet1'!G2"
'    Let Ws2.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").Value = Evaluate("=IF(ISERR(" & Ws2.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").Address & ")," & """""" & ",IF(" & Ws2.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").Address & "=0," & """""" & "," & Ws2.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").Address & "))")
'    Let Ws2.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").NumberFormat = "d.mmm yy"
    Let Ws1.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").Value = "=" & "'" & ThisWorkbook.Path & "\[" & strWb & "]Sheet1'!G2"
    Let Ws1.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").Value = Evaluate("=IF(ISERR(" & Ws1.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").Address & ")," & """""" & ",IF(" & Ws1.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").Address & "=0," & """""" & "," & Ws1.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").Address & "))")
    Let Ws1.Range("G" & Ws2.Range("A2").Value + 1 & ":L" & Ws2.Range("A" & Lr & "").Value + 1 & "").NumberFormat = "d.mmm yy"
    
    Let strWb = Dir
   Loop While strWb <> ""
End Sub

_._____________________

Here for example is one of your data worksheets ( "Ravi.xlsx" )

Using Excel 2007 32 bit

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	S No	Item	Price	Qty	Total	Date Distributed	Task1	Task2	Task3	Task4	Date Tasks Completed	Date Consolidated	Comments	Team Member
2	26	A2	$ 35.00	8	$ 280.00	17. Mrz 18	Done	N/A	Done	N/A	17. Mrz 18			Ravi
3	27	A6	$ 78.00	63	$ 4,914.00	17. Mrz 18	Done	N/A	Done	N/A	17. Mrz 18			Ravi
4	28	B2	$ 11.00	47	$ 517.00	17. Mrz 18	N/A	Done	N/A	Done	17. Mrz 18			Ravi
5	29	B6	$ 96.00	8	$ 768.00	18. Mrz 18							Need more information	Ravi
6	30	C2	$ 74.00	63	$ 4,662.00	18. Mrz 18							Need more information	Ravi
7	31	C6	$ 365.00	47	$ 17,155.00	20. Mrz 18							Need more information	Ravi
8	32	D2	$ 33.00	8	$ 264.00	20. Mrz 18	N/A	Done	N/A	Done	20. Mrz 18			Ravi
9	33	D6	$ 55.00	63	$ 3,465.00	22. Mrz 18	Done	N/A	Done	N/A	22. Mrz 18			Ravi
10

Worksheet: Sheet1

Here is the results in the master file for the area near that persons entry
Using Excel 2007 32 bit

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	N
25	24	D4	$ 22.00	41	$ 902.00	20. Mrz 18	N/A	Done	N/A	Done	20.Mrz 18	Ramesh
26	25	D8	$ 332.00	32	$ 10,624.00	22. Mrz 18	Done	N/A	Done	N/A	22.Mrz 18	Ramesh
27	26	A2	$ 35.00	8	$ 280.00	17. Mrz 18	Done	N/A	Done	N/A	17.Mrz 18	Ravi
28	27	A6	$ 78.00	63	$ 4,914.00	17. Mrz 18	Done	N/A	Done	N/A	17.Mrz 18	Ravi
29	28	B2	$ 11.00	47	$ 517.00	17. Mrz 18	N/A	Done	N/A	Done	17.Mrz 18	Ravi
30	29	B6	$ 96.00	8	$ 768.00	18. Mrz 18						Ravi
31	30	C2	$ 74.00	63	$ 4,662.00	18. Mrz 18						Ravi
32	31	C6	$ 365.00	47	$ 17,155.00	20. Mrz 18						Ravi
33	32	D2	$ 33.00	8	$ 264.00	20. Mrz 18	N/A	Done	N/A	Done	20.Mrz 18	Ravi
34	33	D6	$ 55.00	63	$ 3,465.00	22. Mrz 18	Done	N/A	Done	N/A	22.Mrz 18	Ravi
35	34	A9	$ 12.00	65	$ 780.00	22. Mrz 18						Sangeeta
36	35	B9	$ 45.00	47	$ 2,115.00	22. Mrz 18	Done	N/A	Done	N/A	21.Mrz 18	Sangeeta

Worksheet: Sheet1

Here are the full results in the master workbook, "zMaster.xlsm", after running the code with your 5 data files.
http://www.excelfox.com/forum/showth...0571#post10571

The data files should be in the same Folder as the Master File. The code should go in the Master workbook
MasterFileAnd5DataWorkbookFolder.JPG : https://imgur.com/za3oGLi
MasterFileAnd5DataWorkbookFolder.JPG

Notes:
_ The code uses the second worksheet column 1 temporarily
_ The data worksheets can be open or closed when the code is run. They are not opened by the code. The data from them is taken whilst they are closed. ( But the code will also work if the data files are open )
_ This code assumes that serial numbers are always continuous. If that was not always the case , then the code would need to be modified
_________________________

I am not 100% clear on excactly what should happen to the date in Cloumn L, and also I am not sure exactly what should be done to the date column in the data worksheets. If you clarify that and need more help I will take another look.

Alan

Ref:
https://www.thespreadsheetguru.com/t...a-given-folder