Copy Data from different sheets weekly into a master list with data of the whole year

I hoped some experts here can help me with this.

We have folders of daily cash collections stored in such a manner, year\month. In every month, we will have worksheets sent by the end user to the finance dept, naming it using mmdd. (The folder in the drive will reads: C:\Daily Cash Collection\2013), (C:\Daily Cash Collection\2013\0104.xlxs), (C:\Daily Cash Collection\2013\0115.xlxs). I intend to put the master list outside the year folder, meaning, in the Daily Cash Collection folder (C:\Daily Cash Collection\DCC_2013.xlsm). When I have a new folder for year 2014, my master list will be here (C:\Daily Cash Collection\DCC_2014.xlsm)

I am looking to automate this opening of all the daily worksheets, select all data except the header row, and copy it into a master list (which will be data for the whole year, with 3 months of the previous year data).

The data in the daily sheets, it will have collections of the same Debit Note number from the file sent earlier. Meaning, if the file was sent on 0104, there is a DN0114-0002, collection of $50. In another daily sheets 0115, it will also have a collection of DN0114-0002 of $20. This 2nd information of $20 will also need to be captured as the payment in 0301 is partial and incomplete.

I will need to copy the daily sheets into the master list every now and then. Is there a way to check and copy the daily sheets and not repeating it and missed out one?

In another words, if I had already copied Jan sheets into the master list, will it look for the next worksheet that I had not copy and copy according the DN number? (it will be in running number but sometimes will have DN of the previous month due to the partial payment ealier).

Alternatively, if this is too complicated, how to insert a macro to copy all cells except the header (will be in fixed column and the first row will be fixed) from an open daily sheet, find the last row in the master list and copy it to the master list, and after copying, unclear the selection of the daily sheet and close the daily? Where can I put this macro as the daily sheet is from the end user. I can only put it into my master list, but my problem is, the file name of the daily sheet is not fixed, it depends on the day the end user saved and email the data to Finance Dept.

DCC foler.jpg

Originally Posted by millz

It's unclear what you want to do when there is a same DN number appearing in a later file, so nothing was done for that part. This sample now only collects unique DN number from the workbooks that you have. (RE)Place this exactly in the folder as per your screenshot, open and try clicking the button. See if this is what you want to do, before we proceed to handling the same DN numbers.

Dear millz,

Thank you for the codes. It works but unable to capture the same DN number if this DN was paid in few payments. Meaning, for example, DN0114-0008, we are supposed to collect $300, but on 20 Jan 14, only $84 was collected. Then, maybe on 2 Mar 14, we received another payment ** $16 sent to us in worksheet 0310. Then, in another worksheet 0320, we receive another payment ** $200 with this DN0114-008. In another words, this DN0114-0008 will appear on 3 different worksheets due to payment was received 3 times. Are we able to just copy whatever on the daily cash worksheet, for example, after row 2 (in my sample worksheet), till the end ** the worksheet with a DN on column B.

The reason being, on the original worksheet, the last row ** column A will have a name ** download by and we do not require this name to be copy over to the master list.

Dear Millz,

I am sorry if I really confused you.

What I had mentioned in the beginning, I mean, the daily collection sheets will be email to us every now and then, there is no fixed date. So, when we received the data, we will do a copy into the master list every now and then. Therefore, if in the later of the week, we received files again, we will copy the data from the daily sheets into the master list again. But I will not copy the data from the sheets that I had already copied as we had already done some calculations on those DNs that we had already copied into the master list. (this line means, cannot open those old files and copy the data by overwriting it)

2nd issue, even though I mentioned that the Debit Note number will be in running sequence, but when we have collections of the Debit Note that was paid partially, the same DN no will appear few times and this SHOULD BE COPY into the master list as well.

We do not intend to move the copied Daily collection sheets to another file to differentiate between copied and uncopy.

Currently we are opening every sheet one by one, select all data and copy into the master list. By doing this manually, we need to be alert not to miss out any sheet, and not to overwrite the master list by putting the cursor onto the wrong row.

Originally Posted by millz

I don't know of a better way to "find out" how a record has been previously copied, so this example now inserts the file name where a record is copied from into column E. If a file name is found to be "previously copied", that file will be ignored.

Try and see if the results are satisfactory. There is also a segment of code in there that highlights the "same" DN number in red, if you don't want that to happen just remove that segment of code.

Dear millz,

Thank you for your help. I know this is not easy and appreciated that you had spent time looking into it.

Your code can works in my sample file, but when I wanted to try and make it work in real file, it don't seems to move. In actual file, I have data from Column A till column AB.

I will have 12 folders (Jan till Dec) and inside each folder, will have more than 10 workbooks of different dates.

From your code, I have no idea where should I make the amendments as we will need to create database for the past years.

I had attached a sample view of the folders.Folder View.jpg

Dear Millz,

Thanks for the amendment. I have 2 more little wish, as I totally do not understand how the code works, therefore, unable to edit it myself.

1) Can the vba automatically close the file after copying without saving? I have to answer all "Don't Save" when I tried to run the file and after I clicked "CANCEL", instead of cancelling, the file was open :O. So, I will have to close the file one by one.

2) Can the vba copy from row 4 onwards? And how to change the codes if I intend to use this vba to copy another file if I need to copy from row 2 instead?

Thank you. Even at this stage, you are GREAT!!

Dear Milliz,

Thank you for explaining. I shall close this thread as basically the objective was met. Just need some fine tuning here and there.

Your great patience was appreciated. Have a nice weekend ahead.