Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

MOVE ENTIRE ROW BASED ON CELL VALUE TO NEW SHEET AND DELETE FROM OLD SHEET AUTIOMATICALLY

Copper Contributor

Hello,

 

I have been having some trouble with getting this to work on my end.

 

My goal is to have Column G (STATUS) after being marked as "Done" be moved to a separate sheet within the workbook for tracking purposes but deleted from the original sheet automatically.

 

Can anyone help or give any insight on how to go about this? I have successfully used automate to move the rows to another sheet but cannot get them deleted from the original sheet.

 

Regards,

Javawn

2 Replies

@javawn 

Can anyone help or give any insight on how to go about this?

 

Let me take a crack at the "insight" side of things.

 

I'm a believer in keeping transactional history in one place unless there's some compelling reason not to. And, for that matter, in making the history really complete. In this instance, for example, I see you already have three columns with dates. Why not change the column headed "Status" to read "DateDone" or just plain "Done" and record a date when it concludes.

This accomplishes:

  • First, a more precise and complete history of whatever it is that you're tracking here (bookings?).
  • Second, you can use the Data tool bar filter button, to hide all with Done "dates" in the past.

 

But you also always have at hand the full data set just by changing the setting on the filter. No switching back and forth between sheets.

@javawn You should be able to accomplish this via the use of Office Scripts. If you look at the automate tab, you should be able to find sample scripts, including one called "Move Rows Between Tables". You should be able to modify this sample to do what you want.

 

Then, you'll be able to simply run this script to move rows between sheets, and delete them as you have asked.

 

I hope this helps!