COPY PASTE DATA FROM MASTER WORKBOOK TO ALL WORKBOOKS IN THE FOLDER BASED ON CRITERIA

  • Hello everyone,


    I was recently transfered to planning department in my company, and I found myself drowning in about 40 excel files to manipulate manually, for every small change I nedd to do for an item on a machine. That is impossible for me as it will quickly create confusion regarding what I already did in one file but didn't modify in the other/s.

    What I have to work with:

    1. I have a file named Pressing in real time, which I change manually as per need of the factory floor. Every machine is listed in column A of the sheet Master, and it has multiple rows, as one machine usually does two to five items in one day.

    2. Then after I mannually update Pressing in real time I have to go and mannually update the specific machine workbook, which are in the same folder. For every machine there is a workbook, named with the machine mumber, so from 1 to 23. As moving more items from one machine to another may occur two or three times a day, it is very easy to mess things up, by opening, cutting, copying pasting....

    What I would very badly need at the moment:

    1. I will manually update the wrkbook Pressing in real time, specifically the sheet Live pressing

    2. I would need a macro that I would associate to a command button on the Live pressing sheet, and when I click it it would have to copy and paste rows in the machine workbooks, based on column A criteria. SO the rows which in column A in MAster have number 17 listed, would have to be pasted into the workbook n. 17. The rows which have 19 in column A in MAster would have to be pasted in workbook named 19., and so on for every machine listed in column A

    3. The master sheet, has also the column DAYS NEEDED. Now, I don't know if this is feasable. but I would also like that when the loop finds the number 2 listed in column H of the live pressing sheet , when copying the rows into the respective machine wkbks, I would like to paste them with one blank row underneath them ( because that item will take two days on the machine).



    I attach the example file with fictionary data...I really hope someone can help me, as otherwise it can be very difficult not to mess up in planning the machines for the floor personnel... thank you very much in advance, any help will be highly appreciated.

  • Hello Clairexcel,


    To streamline your admin work, wouldn't it be a better idea to have all your Machine worksheets in the "Pressing" workbook.

    You would be reducing the amount of resource used and rather than opening and closing numerous other workbooks, you could just go to the required machine worksheet instantly. Automating this would easier and would reduce the amount of resource and time needed to carry out each procedure. The data from the Master sheet would be instantly transferred to the various machine worksheets on the click of a button.

    Automating it with the number of separate workbooks that you have would take considerably longer to execute.


    What do you think?


    Cheerio,

    vcoolio.

  • Hello Clairexcel,


    Here's a sample of what I mean using this code:-

    in the attached sample.

    Click on the blue button to see how it works.


    I hope that this helps.


    Cheerio,

    vcoolio

    Clairexcel.xlsm

  • vcoolio, It is perfect , not only copies the rows pertaining the machine but also leveas one blank row if priority number is 2.

    I really can't express how much you have helped me with this file, I really couldn't manage the amount of work with copy/paste in 23 files, without making errors... this file will save my work.

    A huge thank you!

  • You're welcome Clairexcel. I'm glad to have been able to assist and thanks for the feed-back.

    Hopefully, what may have taken you a day to do previously can now be done in a matter of a few minutes!


    Cheerio,

    vcoolio.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!