Move multiple rows from one table to another using conditional trigger

[ItsNursinTime]

Hello,



Here is the issue I am having, I built a workbook containing several tables, each table on their own sheet. The purpose of the workbook is too assist with tracking of patient lines. How the table functions is that when a new line is placed on a patient, a staff member will add that line to the table using a MS Forms/Power Automate flow. At that time, the table column "status" would be marked as "Maintaining" additionally, the row would contain the patient's unique ID and current unit. Should the patient transfer to a different unit, a new row would be added with updated information. The original row "status" column would be changed to "transferred" but remain on the table for tracking purposes. This process could continue through multiple transfers depending on the patient's stay and so would need to remain dynamic. When the line is finally ready to be pulled, the "status" column would be marked as "Discontinued" which would then trigger a VBA to find all the rows with a matching Unique ID and move them all to an archived table and subsequently delete them from the active lines table.



In summary: I'm looking to make a VBA that will move multiple rows based on a shared "unique ID" and for the flow to trigger when one of the rows containing that Unique ID "status" Column is changed to "Discontinued".



I have been able to build a VBA that will move one row based on the trigger, but I can't seem to find a way to add that additional layer of referencing the unique ID of the row that is triggered to take all the matching rows containing the Unique ID with it.



For Reference:

Active line table name is: "CVL"

Active Line Sheet name is "CVL"

Archived Line table name is "Archived_CVL"

Archived Line sheet name is: "Archived_CVL"

Status column name: "Status" Header location I3, Data Range: I4:I

Unique ID column name: "Unique ID" Header location A3, Data Range A4:A



Both the active table and the archived table have the same column headers and the same row/column locations. (essentially the tables are just a copy/paste of each other)

All information in the table are just example data and not actual patient information, in case anyone was concerned about that.



Any help would be greatly appreciated, this is the last hurdle and then I can get this thing rolling!

 

[ItsNursinTime]

Anyone able to help with this?

 

[ItsNursinTime]

Anyone with VBA knowledge able to help?