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

[RaghavendraPrabhu]

I have had a look at various data merging macros that merge data from different workbooks into the master workbook. They work but are not doing what I want.

Please see 40.zip

I want to copy column G to column L where Column K has a date and column L is blank from the unique workbooks into the Masterfile

Paste this data into the Masterfile, lining with the serial numbers in column A. Update the column L with the date the macro is run.

Go back to the unique file, update the date in column L and save and close the file.

In Raghu.xlsx, record with Serial number 3 and 8 need not be copied as they have not been completed.

I physically copied the data I want to be copied from Raghu.xlsx into zMaster.xlsm

Thanks in advance and regards

Raghavendra Prabhu19951996

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836 (https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195)
https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110 (https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533)
https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468 (https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468)
https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411 (https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411)
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384 (https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378)
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305 (https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535)
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533 (https://www.eileenslounge.com/viewtopic.php?p=312533#p312533)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Hi Raghavendra

I think this code will do some of what you want


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
26A2
$ 35.00
8
$ 280.00
17. Mrz 18DoneN/ADoneN/A
17. Mrz 18Ravi


3
27A6
$ 78.00
63
$ 4,914.00
17. Mrz 18DoneN/ADoneN/A
17. Mrz 18Ravi


4
28B2
$ 11.00
47
$ 517.00
17. Mrz 18N/ADoneN/ADone
17. Mrz 18Ravi


5
29B6
$ 96.00
8
$ 768.00
18. Mrz 18Need more informationRavi


6
30C2
$ 74.00
63
$ 4,662.00
18. Mrz 18Need more informationRavi


7
31C6
$ 365.00
47
$ 17,155.00
20. Mrz 18Need more informationRavi


8
32D2
$ 33.00
8
$ 264.00
20. Mrz 18N/ADoneN/ADone
20. Mrz 18Ravi


9
33D6
$ 55.00
63
$ 3,465.00
22. Mrz 18DoneN/ADoneN/A
22. Mrz 18Ravi


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
L
M
N
O

25
24D4
$ 22.00
41
$ 902.00
20. Mrz 18N/ADoneN/ADone
20.Mrz 18Ramesh


26
25D8
$ 332.00
32
$ 10,624.00
22. Mrz 18DoneN/ADoneN/A
22.Mrz 18Ramesh


27
26A2
$ 35.00
8
$ 280.00
17. Mrz 18DoneN/ADoneN/A
17.Mrz 18Ravi


28
27A6
$ 78.00
63
$ 4,914.00
17. Mrz 18DoneN/ADoneN/A
17.Mrz 18Ravi


29
28B2
$ 11.00
47
$ 517.00
17. Mrz 18N/ADoneN/ADone
17.Mrz 18Ravi


30
29B6
$ 96.00
8
$ 768.00
18. Mrz 18Ravi


31
30C2
$ 74.00
63
$ 4,662.00
18. Mrz 18Ravi


32
31C6
$ 365.00
47
$ 17,155.00
20. Mrz 18Ravi


33
32D2
$ 33.00
8
$ 264.00
20. Mrz 18N/ADoneN/ADone
20.Mrz 18Ravi


34
33D6
$ 55.00
63
$ 3,465.00
22. Mrz 18DoneN/ADoneN/A
22.Mrz 18Ravi


35
34A9
$ 12.00
65
$ 780.00
22. Mrz 18Sangeeta


36
35B9
$ 45.00
47
$ 2,115.00
22. Mrz 18DoneN/ADoneN/A
21.Mrz 18Sangeeta
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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10571#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
2007

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/the-code-vault/2014/4/23/loop-through-all-excel-files-in-a-given-folder

[RaghavendraPrabhu]

Once Again Doc Elstein,

Thank you very much for your input. I am sure this will save a lot of problems we are experiencing at our work.

The date column L should have the date the macro was run on for the record's data that was copied.

For Example in Raghu.xlsx, record with serial no.3 and 8 are not completed. When they are completed, the column L for these two records should have the date they were copied.

In zMaster.xlsm, again the column L should have the date the records were copied.

Hopefully, from Monday, we will have fewer problems thanks to your input and help.

Regards
Raghavendra

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836 (https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195)
https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110 (https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Hi Raghavendra,

...I am sure this will save a lot of problems we are experiencing at our work.
we will have fewer problems thanks to your input and help. I think often automaton helps keep things in order and correct, which is an extra Bonus to the saving of time which it should also give
_._____________________________________________---


..The date column L should have the date the macro was run on for the record's data that was copied.
In zMaster.xlsm, again the column L should have the date the records were copied...
I expected It was something like that.

My last code attempt may not be the most efficient as , as far as I know, it is not possible to write information to a closed file, so it would be necessary to open the data files to add the data in column L in the data files anyway.

But for now I will just modify the existing code.
Towards the start of the Looping through the data workbooks the data files are now opened.
The closed workbook references will still work even with the workbook open, so the same data is copied across.
The updated date information is included now in the master Workbook and the data workbooks.

So for example , I ran the latest code today, ( the 24th March ) , using the data files supplied in Post #1


Master Worksheet:
The master worksheet after running the code to merge data into it from the data worksheets, looks like this here:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10576#post10576

Data worksheet example, ( For Workbook “Ravi.xlsx” , first worksheet)
Here is the data worksheet Before the code runs

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
26A2
$ 35.00
8
$ 280.00
17. Mrz 18DoneN/ADoneN/A
17. Mrz 18Ravi


3
27A6
$ 78.00
63
$ 4,914.00
17. Mrz 18DoneN/ADoneN/A
17. Mrz 18Ravi


4
28B2
$ 11.00
47
$ 517.00
17. Mrz 18N/ADoneN/ADone
17. Mrz 18Ravi


5
29B6
$ 96.00
8
$ 768.00
18. Mrz 18Need more informationRavi


6
30C2
$ 74.00
63
$ 4,662.00
18. Mrz 18Need more informationRavi


7
31C6
$ 365.00
47
$ 17,155.00
20. Mrz 18Need more informationRavi


8
32D2
$ 33.00
8
$ 264.00
20. Mrz 18N/ADoneN/ADone
20. Mrz 18Ravi


9
33D6
$ 55.00
63
$ 3,465.00
22. Mrz 18DoneN/ADoneN/A
22. Mrz 18Ravi


10
Worksheet: Sheet1

Here the same worksheet After the code has run

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
26A2
$ 35.00
8
$ 280.00
17. Mrz 18DoneN/ADoneN/A
17. Mrz 18
24.Mrz 18Ravi


3
27A6
$ 78.00
63
$ 4,914.00
17. Mrz 18DoneN/ADoneN/A
17. Mrz 18
24.Mrz 18Ravi


4
28B2
$ 11.00
47
$ 517.00
17. Mrz 18N/ADoneN/ADone
17. Mrz 18
24.Mrz 18Ravi


5
29B6
$ 96.00
8
$ 768.00
18. Mrz 18Need more informationRavi


6
30C2
$ 74.00
63
$ 4,662.00
18. Mrz 18Need more informationRavi


7
31C6
$ 365.00
47
$ 17,155.00
20. Mrz 18Need more informationRavi


8
32D2
$ 33.00
8
$ 264.00
20. Mrz 18N/ADoneN/ADone
20. Mrz 18
24.Mrz 18Ravi


9
33D6
$ 55.00
63
$ 3,465.00
22. Mrz 18DoneN/ADoneN/A
22. Mrz 18
24.Mrz 18Ravi


10
Worksheet: Sheet1

Here the code:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10577#post10577


Alan

[RaghavendraPrabhu]

Doc Elstein

Thank you this is what I wanted. But, it is not updating th xMaster the second time. Doing it for once.

Input more new work for members and when clicked the first macro, it is working and updating the unique files.

Once the unique files are completed, this macro is not updating the xMaster second time.

Regards

Raghavendra

Regards

Raghu

[DocAElstein]

Hi Raghu, Hi Raghavendra
Sorry I do not understand.

Try to explain carefully what should happen.

If possible show me a Before and then an After

Before : what you have:
After: You do this manually to show me what the code should do


Take your time. I am busy today. From Tomorrow I can look again

Alan

[RaghavendraPrabhu]

Hi Doc Doc Elstein

Please see the attached files.

It is not working when the macro is run the second time.

No hurry. I am away from work for a week due to Easter etc.

Take your time to fix and thank you for your valuable time and knowledge input.

Regards
Raghavendra

[DocAElstein]

Hi Raghavendra
I may be beginning to understand what you want.
But some things are still not clear to me.

I do not know which codes you are running when and to which data is being used when.

You need to carefully explain a full run from start to finish of whatever it is that is being done.

_1) First run of code ( which one?? )
Which data is used.
What are the befores and afters

_2) What do you do now ? Run a different code? Which one
Which data is used.
What are the befores and afters

_3) you now run a code a second time ( which one?? )
Which data is used.
What are the befores and afters

_4) What do you do now ? Run a different code? Which one
Which data is used.
What are the befores and afters



Please remember it is obvious to you what is being done and when and which codes you are using. You know the project well
I am having to try and guess often to which codes you are referring and when.

I do not know what is the typical data structure.
I do not know what might be typical data structure in the future.
I do not know how exactly new data is added.
I do not know the typical sequence of events
That is why I need to see befores and afters for all situations. Otherwise I must guess, and I will probably guess wrong..


A clear explanation of exactly what is happening will also help anyone else to benefit from the Thread in the future.
Every time you refer to a code, name it, so as to avoid confusion.
Explain the exact sequence of events. Give example data for each stage showing exactly what you have before and fill in manually to show what you want the code to produce as the after.

Keep the example data to the minimum. But choose it carefully so that it can be use do demonstrate and check all possible scenarios.

I expect that when I finally know exactly what the requirement is, then I may need to start again from the beginning with a new code or codes.
So please take the time to explain very carefully with examples, and then I will try to do this for you over the next week or so.

Alan

[RaghavendraPrabhu]

Hi Doc Elstein,

I have sorted out the problem. It is a bit convoluted, but it is working. Please have a look at the code below.



Option Explicit
Sub BringInAllCompletedData()
Call SortAllFiles
Call LoopThroughDirectory
Call UpdateDateInSheet1ColK
Call UpdateOriginalData
Call ClearSheet1
End Sub

'https://www.mrexcel.com/forum/excel-questions/471802-vba-open-file-run-code-close-save-open-next-file.html
Sub SortAllFiles()
Dim folderPath As String
Dim filename As String
Dim wb As Workbook

Application.DisplayAlerts = False

folderPath = ActiveWorkbook.Path & "\" 'change to suit
If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
filename = Dir(folderPath & "*.xlsx")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)
'Call a subroutine here to operate on the just-opened workbook
If filename = "zmaster.xlsm" Then
Exit Sub
Else
Call SortSheet1InAllFiles
End If
filename = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub SortSheet1InAllFiles()
Dim MyFile As String
Dim eRow As Long
Dim RowsConsolidated As Long
Dim LastRow As Long
Dim i As Long

eRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Cells.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("K2:K" & eRow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:N" & eRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Save
Range("A1").Select
ActiveWorkbook.Close

End Sub

'http://www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/

Sub LoopThroughDirectory()
Dim MyFile As String
Dim eRow As Long
Dim LRL As Long
Dim LRK As Long
Dim i As Long

Dim FilePath As String
FilePath = ActiveWorkbook.Path & "\"

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Sheet1").Activate
MyFile = Dir(FilePath)
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (FilePath & MyFile)
LRK = Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).Row 'Column L
LRL = Cells(Rows.Count, 12).End(xlUp).Offset(1, 0).Row 'Column K

For i = LRL To LRK
Range("A" & LRL & " : " & "K" & LRK).Copy
Next
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, 11))


If MyFile = "zmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (FilePath & MyFile)
For i = LRL To LRK - 1
If Range("L" & i).Value = "" Then
Range("L" & i).Value = Date
Columns("L:L").NumberFormat = "[$-C09]dd-mmm-yy;@"
End If
Next
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

MyFile = Dir
ActiveWorkbook.Save
Loop

Columns("A:D").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A" & eRow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:D" & eRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub UpdateDateInSheet1ColK()
Dim eRow As Long
Dim i As Long

Sheets("Sheet1").Activate
eRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For i = 2 To eRow
If Range("K" & i) <> "" Then
Range("L" & i).Value = Format(Date, "dd/mmm/yyyy")
End If
Next
End Sub

'https://www.youtube.com/watch?v=AzhQ5KiNybk
Sub UpdateOriginalData()
Dim i As Integer
Dim j As Integer
Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim SNo As Double

LastRow1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LastRow2 = Sheets("OriginalData").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow1
SNo = Sheets("Sheet1").Cells(i, "A").Value
Sheets("OriginalData").Activate
For j = 2 To LastRow2
If Sheets("OriginalData").Cells(j, "A").Value = SNo Then
Sheets("Sheet1").Activate
Sheets("Sheet1").Range(Cells(i, "G"), Cells(i, "L")).Copy
Sheets("OriginalData").Activate
Sheets("OriginalData").Range(Cells(j, "G"), Cells(j, "L")).Select
ActiveSheet.Paste
End If
Next j
Application.CutCopyMode = False
Next i
Sheets("OriginalData").Activate
Cells.Select
ActiveWorkbook.Save
Selection.Columns.AutoFit
Range("A1").Select

End Sub

Sub ClearSheet1()
Dim eRow As Long

Sheets("Sheet1").Activate
eRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Range("A2:O" & eRow).Select
Selection.ClearContents
Selection.Columns.AutoFit
Range("A1").Select
ActiveWorkbook.Save
End Sub


Thank you for all your suggestions and input. What I have done is a long drawn process. Maybe it can be written into one macro.

I will consider this as solved.

Thank you for all your help, time and patience.

Regards

Raghu

[DocAElstein]

Hi Raghu,

Thanks for the feedback and sharing your solution.
You have clearly worked hard to solve your problem. Great , well done! :)

I had a quick look through your files. It is still a bit difficult for me to understand what you are doing or wanting to do: I do not know your project so to understand would need a clear explanation from start to finish of what you are doing or wanting to do.
I see lots of codes and files but have no idea which codes and files are used for which and when. I have to work very hard to try to guess what is going on. This will be obvious to you, but without a careful explanation from you it will be impossible for anyone like me to have any idea what is going on.

But you appear to have a solution , so great.
The codes may not be the most efficient, but I think those considerations are less and less relevant for an application like yours as computers get faster and have larger memories etc..
It can be advantageous to have a long drawn out process, as this will be easier for you to follow and modify or de bug in the future. I personally prefer long drawn out codes for those reasons, but that is just my opinion, and I am just a hobby computer user and am not a professional programmer.

I expect you may be doing a lot of unnecessary sorting of data, but I cannot be sure as I do not understand what and when things are to be done.

Good luck with the project, and let us know if you need more help.
But please in future try to explain more clearly in words exactly what is to be done , and when and which data is being used for which code .. etc.. etc… You have supplied some excellent detailed Test data and files. It is a shame more explanations to them were missing: Because an explanation was missing, I still could not use them effectively to give a better solution.
You simply need to write in words the action that you take to produce the files, which codes are run and when etc… You need to understand that for someone that does not know the project details as you do, will need them to be explained carefully.

For someone with little MS Excel VBA experience you have done very well to get your working solution. Well done again.
:)

Alan

[RaghavendraPrabhu]

Hi Alan,
The project is simple. Our team consists of 16 team members.

Our team leader imports system generated data into the zMaster.xlxm file populating columns B, C and D. The file he imports has nearly 1000 lines daily.

He then runs a macro to populate column N with the names of the team members. Column A populates automatically just a serial number.

He then distributes the work to all the 16 members by running the macro which I used from the following site and you helped modify.

https://stackoverflow.com/questions/46368771/how-to-create-a-new-workbook-for-each-unique-value-in-a-column?rq=1

The above macro also populates the column F.

Once the work is distributed, team members completed their tasks and populate columns G to K and Column M

Later in the evening the team leader then runs the following and consolidates the work. That way he knows the work that is completed.



Sub BringInAllCompletedData()
Call SortAllFiles
Call LoopThroughDirectory
Call UpdateDateInSheet1ColK
Call UpdateOriginalData
Call ClearSheet1
End Sub


Previously all this was done with MS Access. Our office recently had new hardware installed and for financial reason decided not to have MS Access and so we had to come up with this solution.

I had to run the macro SortAllFiles because for some reason, if sorting was not done data was not being imported into the zMaster.xlsm

Now it is all working well. I will try and put it all in one macro later down the track.

I hope this explanation has helped you understand my project.

One small question please. How do I modify the macro to use a template named template.xlsm placed within a folder in the same folder instead of creating a xlsx file?

https://stackoverflow.com/questions/46368771/how-to-create-a-new-workbook-for-each-unique-value-in-a-column?rq=1

The path were the template is, is ThisWorkbook.Path & ""Template”\”Template.xlsm

Alan, thank you for all your encouragement and feedback.

[DocAElstein]

Hi Raghu,
Most things seem very simple when you are familiar – A good friend of mine I had to help a lot in the school and still do in further life as he is very slow at understanding the simplest things. But he was very motivated and now is a very successful pilot flying the largest passenger machines – he is very familiar with flying machines and all that goes with it, but that’s about all he can do, Lol.. :) ( Even amongst his colleagues he is still known as a bit of an idiot ( but a confident Pilot I might add )
I have a large File used daily by my wife for her necessary diets. I find it so simple that it bores me to try to explain it to her. But this winter I have been trying for days to explain it to her so that she takes over it all herself.. but after days she still understands very little… I expect I will give up and try again next Winter, Lol…

I am not a professional programmer, but some I know tell me that the most difficult thing they ever have to do is understand someone else’s code. So once again , it is very good that you have persevered and got your own solution. I personally would always use a more complicated code that I undrsatand rather than a “seemingly” simpler which I do not understand. My goal is always to have a clever/ simple code which I also understand. Only sometimes I manage that, lol..

For two years I have been learning VBA programming now and I am continually surprised how many very experienced programmers use some very elegant codes which they do not understand. I find that quite worrying.. Much of the coding controlling our lives is understood by nobody. I can’t help thinking that will lead to a catastrophe one day….


_.___________-

Thanks for the further explanation.
I see now that the start point, as far as the code discussions in this thread are concerned is the running of the final code from your other Thread, http://www.excelfox.com/forum/showthread.php/2237-Make-macro-create-unique-files-only-once-If-files-exist-amend-them#post10543 . That is perhaps obvious with hindsight , but it wasn’t initially.
So, just for future reference…
From Zip Foldere, “WorkDistributed 15MAR18”
running the macro which I used from the following site and you helped modify.
https://stackoverflow.com/questions/46368771/how-to-create-a-new-workbook-for-each-unique-value-in-a-column?rq=1
The above macro also populates the column F.

“WorkDistributed 15MAR18”
“Work Distribution”
All Files there are the “Afters”
File “zMaster.xlsm” would be the “Before” if the dates were removed from column F
So this was your after
Row\Col
D
E
F
G
H

1
Qty
Total
Distributed
Task1
Task2


2
22
$ 1,210.00
15. Mrz 18


3
7
$ 95.62
15. Mrz 18


4
5
$ 64.95
15. Mrz 18

Row\Col
E
F
G
H
I

44
$ 33,011.55
15. Mrz 18


45
$ 11.99
15. Mrz 18


46
$ 74,940.99
15. Mrz 18


47


48
Worksheet: OriginalData
If I delete the dates in column F then I get the before:
Row\Col
C
D
E
F
G
H
I
J
K
L
M
N

1
Price
Qty
Total
Distributed
Task1
Task2
Task3
Task4
Completed
Consolidated
Comments
Team Member


2
$ 55.00
22
$ 1,210.00 Raghu


3
$ 13.66
7
$ 95.62 John


4
$ 12.99
5
$ 64.95 Greg


5
$ 8.51
12
$ 102.12 Margaret
Worksheet: OriginalData

If in the before I run the code Sub ExportByName on the before then I get
_ the after ( but with today’s data )
Row\Col
E
F
G
H

1
Total
Distributed
Task1
Task2


2
$ 1,210.00 02.Apr.2018


3
$ 95.62 02.Apr.2018


4
$ 64.95 02.Apr.2018
Worksheet: OriginalData
¬_ In addition to the above change to the master file I get the 4 data files , Raghu , John , Greg, Margaret.
For example “Raghu.xlsx”
Row\Col
E
F
G
H
I
J
K
L
M
N

1
Total
Distributed
Task1
Task2
Task3
Task4
Completed
Consolidated
Comments
Team Member


2
$ 1,210.00 02.Apr.2018Raghu


3
$ 447.64 02.Apr.2018Raghu


4
$ 74,940.99 02.Apr.2018Raghu
Worksheet: Tabelle1
This is the same as the File from Zip File, “WorkDistributed 15MAR18” you gave , but just the date, as expected is different.
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N

1
S No
Item
Price
Qty
Total
Distributed
Task1
Task2
Task3
Task4
Completed
Consolidated
Comments
Team Member


2
1ABC01
$ 55.00
22
$ 1,210.00
15. Mrz 18Raghu


3
5ABC05
$ 7.22
62
$ 447.64
15. Mrz 18Raghu


4
9ABC09
$ 741.99
101
$ 74,940.99
15. Mrz 18Raghu
Worksheet: FromRaghu

So that is the situation finished with the “Work Distribution”

[DocAElstein]

Zip Folder “WorkDistributedAndConsolidated 16MAR18” .. etc…
( and further to final code solution )
So the last post produced the “work” which was distributed to the team members at the start of the day.
At some time through out the day the members may complete their work and so…..” ….Once the work is distributed, team members completed their tasks and populate columns G to K and Column M……”

So for example from the last post we had the situation for “Raghu.xlsx” of this before any entry by you ( Raghu ).. _..
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10596#post10596

_... Once the team member ( you, Raghu ) , has completed the work and filled in the worksheet appropriately, those above screenshots could change to something like this:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10597#post10597

I could make a similar set of Files for all team members, having finally_...
Files:
“Raghu.xlsx” https://app.box.com/s/at411zdf611vjuds3kfo6ymdpecfzmis
“John.xlsx” https://app.box.com/s/l05b9but8fc315q9d39nes7s19vi8vdm
“Greg.xlsx” https://app.box.com/s/zoxbm3aok8g27xc0ceaxr084xtore6o4
“Margaret.xlsx” https://app.box.com/s/i28p8ukgggwm4ndb3p692vu6tm0epn09
_...Screenshots:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10598#post10598
Those above would be typical situations at the end of the day, just before the “Consolidating” is to be done:

Consolidating
If I now understand the problem correctly, we want a code or codes to be used at the end of the day
This required coding must:
_ bring the information from columns G-K and M of the data files, for all rows of completed work, that is to say where the team Member has filled in a date in column K. ( This will also be the work not yet shown as consolidated in those data files in column L ).
( This “bringing in of the information for newly completed work at the end of the day” is what we are referring to as “Consolidating”)
_ The current day of the consolidating is to be filled into the data sheets. '_-##)

The reason why my code Sub Consolidate() is not doing what you want is that
_1) I did not know that the code would be run again with additional data added somewhere.
_2) In the example data from post #1 ( http://www.excelfox.com/forum/showthread.php/2238-Copy-data-from-Unique-files-into-Masterfile-all-the-files-in-the-same-folder : 040.zipUniqueNameColumnMovedToTheEnd.zip ) the list of names was in sections for each team member in column N of the master file “zMaster.xlsm”
_3) I probably assumed/ guessed a few other things based on the supplied info

Once again, with hindsight it appears obvious now what is now needed, but initially it is not always obvious..

So I would have a zMasterAfterDistribution which could also be called a zMasterBeforeConsolidation:
File: “zMasterBeforeConsolidation.xlsm”
https://app.box.com/s/818q2ev3owpini2202n3dqp3xxicfeif
Screenshot:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page6#post10599
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10599#post10599

So.... just for fun....
My code alternative:
It does not seem to difficult to do. You have your own code, and in your position I would probably prefer to use a code I had written myself. But for completeness, this is the code I would probably use http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10611#post10611


If I run that code using_..
Files:
“Raghu.xlsx” https://app.box.com/s/at411zdf611vjuds3kfo6ymdpecfzmis
“John.xlsx” https://app.box.com/s/l05b9but8fc315q9d39nes7s19vi8vdm
“Greg.xlsx” https://app.box.com/s/zoxbm3aok8g27xc0ceaxr084xtore6o4
“Margaret.xlsx” https://app.box.com/s/i28p8ukgggwm4ndb3p692vu6tm0epn09
_...Screenshots:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10598#post10598
File: “zMasterBeforeConsolidation.xlsm”
https://app.box.com/s/818q2ev3owpini2202n3dqp3xxicfeif
Screenshot:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page6#post10599
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10599#post10599
_... then I receive these results for the updated master Worksheet_..
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10600#post10600
_.. Those results look very similar to your results for a file from you:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10601#post10601
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10602#post10602

_. And here is a typical data File with the column L updated appropriately with the consolidation date:
“Raghu.xlsx”:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10603#post10603

_....
If I add data as per your description of how your team leader goes about at the start of the _..
_ add data,
_..File: “zMasterAfter1stConsolidationNewData.xlsm” : https://app.box.com/s/ascky2qg47dzl85b4y7l8sy5qmr3goby
Screenshot :
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10604#post10604
_.. and then run code Sub ExportByName() _..
t.-.. I get then The results for
_Updated master:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10605#post10605
_Updated data worksheets
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10606#post10606


For the sake of this demo, I then assume most of the work is then done, so that for example we have data Files just before second consolidation:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10607#post10607

Finally I run the code Sub consolidateToo() a second time
Here the final results:
Master File:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10608#post10608
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10609#post10609
data files:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10610#post10610





_._____


I could not follow the logic of your coding, but as I mentioned it is always difficult to follow someone else’s coding. I could not get a code run of Sub BringInAllCompletedData() to do anything close to what I perceive as required. But I may not be applying it correctly.
I did the new code for completeness.
If I were in your position then I would stay with the code you did yourself, if it is doing what you want.
It is much easier for you to understand and maintain your own code in the future. You have actually done some interesting codes , so thanks again for sharing your solution.


_._______________

Finally I will try to answer your last question in next post.

[DocAElstein]

...
One small question please. How do I modify the macro to use a template named template.xlsm placed within a folder in the same folder instead of creating a xlsx file?
https://stackoverflow.com/questions/46368771/how-to-create-a-new-workbook-for-each-unique-value-in-a-column?rq=1
The path were the template is, is ThisWorkbook.Path & ""Template”\”Template.xlsm.

I am not sure exactly what you are asking here. I am not too familiar with the code you referenced at the stack overflow Forum.
If we are talking about the code, Sub ExportByName(), then it does not make a file if it already exists.
If you always have a file ready at the specified location, then no new file will be made.

Possibly you are asking for when you have no file for a team Member, then rather than make a new one, you open the template and save that with the missing team member file.?

If that is your question, then you would remove
Workbooks.Add
and replace that with coding to open that template file, and then save it with the name for the new member, but remember to save it at the correct File path, which I assume would still want to be that for where the master file is.

If you need more help on that modification then let me know.

Alan

[klimbo123]

Hi Doc Elstein,

I have sorted out the problem. It is a bit convoluted, but it is working. Please have a look at the code below.



Option Explicit
Sub BringInAllCompletedData()
Call SortAllFiles
Call LoopThroughDirectory
Call UpdateDateInSheet1ColK
Call UpdateOriginalData
Call ClearSheet1
End Sub

'https://www.mrexcel.com/forum/excel-questions/471802-vba-open-file-run-code-close-save-open-next-file.html
Sub SortAllFiles()
Dim folderPath As String
Dim filename As String
Dim wb As Workbook

Application.DisplayAlerts = False

folderPath = ActiveWorkbook.Path & "\" 'change to suit
If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
filename = Dir(folderPath & "*.xlsx")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)
'Call a subroutine here to operate on the just-opened workbook
If filename = "zmaster.xlsm" Then
Exit Sub
Else
Call SortSheet1InAllFiles
End If
filename = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub SortSheet1InAllFiles()
Dim MyFile As String
Dim eRow As Long
Dim RowsConsolidated As Long
Dim LastRow As Long
Dim i As Long

eRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Cells.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("K2:K" & eRow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:N" & eRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Save
Range("A1").Select
ActiveWorkbook.Close

End Sub

'http://www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/

Sub LoopThroughDirectory()
Dim MyFile As String
Dim eRow As Long
Dim LRL As Long
Dim LRK As Long
Dim i As Long

Dim FilePath As String
FilePath = ActiveWorkbook.Path & "\"

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Sheet1").Activate
MyFile = Dir(FilePath)
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (FilePath & MyFile)
LRK = Cells(Rows.Count, 11).End(xlUp).Offset(1, 0).Row 'Column L
LRL = Cells(Rows.Count, 12).End(xlUp).Offset(1, 0).Row 'Column K

For i = LRL To LRK
Range("A" & LRL & " : " & "K" & LRK).Copy
Next
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, 11))


If MyFile = "zmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (FilePath & MyFile)
For i = LRL To LRK - 1
If Range("L" & i).Value = "" Then
Range("L" & i).Value = Date
Columns("L:L").NumberFormat = "[$-C09]dd-mmm-yy;@"
End If
Next
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

MyFile = Dir
ActiveWorkbook.Save
Loop

Columns("A:D").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A" & eRow) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:D" & eRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub UpdateDateInSheet1ColK()
Dim eRow As Long
Dim i As Long

Sheets("Sheet1").Activate
eRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For i = 2 To eRow
If Range("K" & i) <> "" Then
Range("L" & i).Value = Format(Date, "dd/mmm/yyyy")
End If
Next
End Sub

'https://www.youtube.com/watch?v=AzhQ5KiNybk
Sub UpdateOriginalData()
Dim i As Integer
Dim j As Integer
Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim SNo As Double

LastRow1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LastRow2 = Sheets("OriginalData").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow1
SNo = Sheets("Sheet1").Cells(i, "A").Value
Sheets("OriginalData").Activate
For j = 2 To LastRow2
If Sheets("OriginalData").Cells(j, "A").Value = SNo Then
Sheets("Sheet1").Activate
Sheets("Sheet1").Range(Cells(i, "G"), Cells(i, "L")).Copy
Sheets("OriginalData").Activate
Sheets("OriginalData").Range(Cells(j, "G"), Cells(j, "L")).Select
ActiveSheet.Paste
End If
Next j
Application.CutCopyMode = False
Next i
Sheets("OriginalData").Activate
Cells.Select
ActiveWorkbook.Save
Selection.Columns.AutoFit
Range("A1").Select

End Sub

Sub ClearSheet1()
Dim eRow As Long

Sheets("Sheet1").Activate
eRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Range("A2:O" & eRow).Select
Selection.ClearContents
Selection.Columns.AutoFit
Range("A1").Select
ActiveWorkbook.Save
End Sub


Thank you for all your suggestions and input. What I have done is a long drawn process. Maybe it can be written into one macro.

I will consider this as solved.

Thank you for all your help, time and patience.Lucky Patcher (https://inro.in/lucky-patcher/) 9Apps (https://inro.in/9apps/) VidMate (https://inro.in/vidmate/)

Regards

Raghu

i can't download the files why ?

[DocAElstein]

i can't download the files why ?
Hi klimbo123
Welcome to excelfox :)
Please explain yourself, in much more detail, if you want any help here, at excelfox
Alan

P.s. If the probem for you is the files from here http://www.excelfox.com/forum/showthread.php/2238-Copy-data-from-Unique-files-into-Masterfile-all-the-files-in-the-same-folder#post10591
Then here they are again at a file sharing site:

WorkCondolidated 18MAR18 _____https://app.box.com/s/tythnc8zge5g1ezqnd03uzzsgufb1mnp
WorkDistributed 15MAR18 ___ https://app.box.com/s/jfpeq908wc122x18vvjub2avtmcrg8p0
WorkDistributedAndConsolidated 16MAR18 : _ https://app.box.com/s/oxih69lkebmenqi6rgshzdhonf1lbddy