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

[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/...-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/...-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]

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/showth...0596#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/showth...0597#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/showth...0598#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/showth...he-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/showth...age6#post10599
http://www.excelfox.com/forum/showth...0599#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/showth...0611#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/showth...0598#post10598
File: “zMasterBeforeConsolidation.xlsm”
https://app.box.com/s/818q2ev3owpini2202n3dqp3xxicfeif
Screenshot:
http://www.excelfox.com/forum/showth...age6#post10599
http://www.excelfox.com/forum/showth...0599#post10599
_... then I receive these results for the updated master Worksheet_..
http://www.excelfox.com/forum/showth...0600#post10600
_.. Those results look very similar to your results for a file from you:
http://www.excelfox.com/forum/showth...0601#post10601
http://www.excelfox.com/forum/showth...0602#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/showth...0603#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/showth...0604#post10604
_.. and then run code Sub ExportByName() _..
t.-.. I get then The results for
_Updated master:
http://www.excelfox.com/forum/showth...0605#post10605
_Updated data worksheets
http://www.excelfox.com/forum/showth...0606#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/showth...0607#post10607

Finally I run the code Sub consolidateToo() a second time
Here the final results:
Master File:
http://www.excelfox.com/forum/showth...0608#post10608
http://www.excelfox.com/forum/showth...0609#post10609
data files:
http://www.excelfox.com/forum/showth...0610#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.