Auto copy row on next blank row on other worksheet if call value is true

[andyred12]

Hello,

I'm trying to make a shift log more user friendly. If I write a job up in sheet "log" and choose "priority" from the drop down menu in column E, I would like to copy that row columns B,C,D into the next available row +1 in sheet "Priority Work". I should also like this to delete if I go back to the log and select "complete" from the drop down list.

Similarly, I would like the same to happen for sheet "Follow Up" if "follow up" is selected from the dropdown.

I've tried pasting numerous bits of VBA of this forum and trying to alter them and failed miserably and I'm pulling out what little hair I have left.

Many thanks in anticipation

Andy

[Mumps1]

Hi Andy. Copy and paste this macro into the worksheet code module. Do the following: right click the "Log" tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your selection in column E. Please note that when you choose "complete", the entire row in the "Log" sheet will be deleted. I think that is what you wanted. If that is not correct, please let me know exactly what you want to do when "complete" is chosen. Try the macro out in a copy of your file.

Please Login or Register  to view this content.

[andyred12]

Hi Mumps1,

Almost perfect! If possible could you just alter it so it keeps all the data in sheet "log" regardless of what column D states, but deletes the data in sheet "priority work" and "follow up" sheet

The idea is to keep all data entered in the shift "log" sheet and delete data in the "priority work" amd "follow up" sheets so that when a priority job, or follow up has been completed it can be removed from the "priority work" or "follow up" sheet by selecting "complete" in the "log" sheet.

Thanks very much for your help....I just wish I'd come to you 4 days ago!

Andy

[Mumps1]

Hi Andy. The only way I can make that work is if each job (row) has a unique identifier such as a job number and that unique identifier is also copied to the other sheet. There has to be some unique identification that can be used to link the data between the sheets. Is this possible?

[andyred12]

Hi Mumps,

Could I just number the rows in column A?

Andy

[Mumps1]

OK. That would work as long as when you enter data in your log sheet you don't use numbers in column A that may already exist in the other 2 sheets. I can make the changes in the file you posted by adding those numbers and I'll post a copy of the revised file with the macro. Because of time constraints today, I'll have to get back to you tomorrow.

[andyred12]

Ok Mumps, thats much apprieciated, thanks

Andy

[Mumps1]

Hi Andy. Try the attached file. Please let me know how it works out.

[andyred12]

Hi Mumps!,

Works perfectly, I'm over the moon. If you are ever in Doncaster I'll get the beers in.

Thanks again

Andy

[Mumps1]

It was my pleasure. Who knows, maybe one day.