VBA - Transfer data to tab based on intials and then delete from tabs once date completed - Help

    I am trying to create VBA that copies over data from a summary tab onto separate tabs based on initials in the summary tab. I only need certain columns transferring over.

    I have attached a mock spreadsheet for visualisation purposes.

    So the all the information is manually added to the summary tab. I then need data from columns C,A,D, N (column N needs to be free text and have the ability to be changed) ,M and H to be transferred to the correct tab based on the initials in column J , and the data is then transferred to the correct tab into columns A-F . Once the work is completed the date complete will be added. Once the date is added I need the row to be deleted from the initial tab but kept on the main summary tab.

    It would make it easier to help if you could attach a file that has data in the Summary sheet and a few of the initials tabs that show the expected results based on that data.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    It looks like you want to copy the data from the Workflow sheet not the Data sheet.

    Quote from LanKez

    Once the work is completed the date complete will be added. Once the date is added I need the row to be deleted from the initial tab but kept on the main summary tab.

    Please explain in detail what you mean by the above quote. What is the name of the initial tab and what is the name of the summary tab?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    yes - so all the data will go in the workflow tab and I want the rows to move to the relevant initials tab so the person can just work off the tab not the main workflow sheet. Then when they add the date complete I want the row to be removed from the initals tab but kept on the workflow tab with it showing the date complete. Hope this makes sense

    I can suggest a macro to do what you want, however, I can foresee some problems.

    1. Currently you have Gill, Bill and Haillie in column A of the Workflow sheet. Both Gill and Bill have the same initial in column J which means that their data would go into the same sheet (AB).

    2. Let's assume that the data for these 3 people is copied to their sheets. Each person will have one job. Then we add another job in the Workflow sheet for Haillie and that data is copied to his sheet. Haillie now has two rows of data in sheet KB. Haillie then enters the complete date in column E to indicate that the first job is finished. You want that row of data deleted from sheet KB and the completion date for that job entered in the Workflow sheet. The problem is that Haillie has two rows of data in the Workflow sheet. How do we determine which of the two jobs has been completed so we can add the completion date? The only way to do this is for each job in the Workflow sheet to have a unique identifier. Will the file reference numbers in column O be unique to each job?

    3. You also have to decide what will trigger the copying of the data from the Workflow sheet to the individual sheets. I believe that the best option would be to have each job copied automatically after the file reference number is entered in column O. This way the jobs will be transferred one at a time as they are entered in the Workflow sheet.

    4. Lastly, you said:

    Quote from LanKez

    I have attached a mock spreadsheet for visualisation purposes.

    You should be aware that a solution that works on a mocked up sheet most often will not work for your actual sheet unless the sample sheet is an exact representation of the actual sheet. If the sample you posted is not an exact representation of your actual sheet, please attach an updated file which is an exact representation.


    Sorry for the lengthy response but in order to get a working solution, all the points I described are very important.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    thank you for your quick reply:

    Answers

    1. The person who will be doing the work is the persons initials in column J , the name eg Haillie is the contact who put in the initial enquiry not the person doing the work. That's why I need the data to copy over to these initials tabs.

    2. the completion date will be added into column E of the initial tabs - I need this completion date to populate column M in the workflow tab - yes the file reference will be unique to each job but not every job will have one

    3. I was hoping the trigger would be when the initials are added to column J - as this is work for that relevant person

    4 The sheet I uploaded is a replica of the one that will be used. I have attached it again


    Let me know if you need anything more :)

    Quote from LanKez

    the file reference will be unique to each job but not every job will have one

    In order for this to work for you, every job must have a unique identifier. The file reference could be used since it will be unique for each job, but this won't work unless every job has a unique file reference. If this is not possible, could another column with a different unique identifier be added?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    I just noticed that in the Unall sheet, the headers are in row 1 instead of row 2 and that the Priority in column F is missing. Is this what you wanted for this sheet?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Working on it.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Try the attached file. I have moved the "Allocated to" column to the end of the Workflow sheet. The reason for this is to reinforce the fact that it must be the last column populated after all the other data in the target row is entered. I have also added the Unique ID column to all the "Initial" sheets. When you select an initial in column P of the Workflow sheet, the data in that row will be automatically copied to the appropriate sheet. When you enter the completed date in column F of the "Initial" sheet and press the RETURN key, the target row will be deleted and the completed date will be entered in the corresponding job in the Workflow sheet based on the ID in column A.

    Files

    LanKez.xlsm 98.73 kB – 114 Downloads

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    That's just what I want , Thank you - quick question if I add data past row 6 it says there is a time debug error and doesn't transfer the data over - do I need to enter anything so it runs for the whole spreadsheet?

    I can't reproduce the error. The macro should run for all rows. It works properly on the file I posted. Are you using the macro on the same file I posted or on a different file? If on a different file, please post a copy of the file that is giving you the error.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    What do I need to add to this vba so the comments column in the Initial tabs "Column E" move to the work flow page Column N when they are added also is there something I need to add so when updates are made to the workflow page they update the rows on the initial tabs??? Any help appreciated :)

Participate now!

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

Register Yourself Login