Move All Matching Patient Rows to an Archive Table with Excel VBA
Question details
Create a VBA event macro that moves every active-table row sharing a patient's Unique ID to an archive table when any matching row is marked Discontinued.
- Product
- Microsoft Excel VBA tables
- Device & OS
- not provided
- Scenario
- A patient may have records in multiple units. When the Status value for one record is changed to Discontinued, all records for that patient must be archived and removed from the active table.
- Observed behavior
- The required result is that every row with the triggered row's Unique ID is copied to the archive table and then deleted from the active table without skipping rows.
Confirm that both Excel tables have the same columns in the same order and are named CVL and Archived_CVL. Also verify that the active table contains columns named Unique ID and Status, and test the event macro with non-sensitive sample records first.
Use a Worksheet_Change event to archive every matching Unique ID
Use this method when changing a Status cell to Discontinued should automatically move all rows for that patient, including rows assigned to different units.
The macro reads the Unique ID from the row whose Status was changed, then loops through the CVL table from the last row upward. Processing rows in reverse order prevents Excel from skipping records after a row is deleted.
This code assumes that CVL and Archived_CVL are Excel tables on the same worksheet or otherwise accessible from the worksheet containing the event code. Both tables must have matching column layouts.
In Excel, right-click the worksheet tab that contains the CVL table and select View Code. Paste the event procedure into that worksheet's code window, not into a standard module.
Paste the following code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) Dim activeTbl As ListObject Dim archiveTbl As ListObject Dim patientID As Variant Dim changedRow As Long Dim i As Long On Error GoTo SafeExit Set activeTbl = Me.ListObjects("CVL") Set archiveTbl = Me.ListObjects("Archived_CVL") If activeTbl.DataBodyRange Is Nothing Then Exit Sub If Intersect(Target, activeTbl.ListColumns("Status").DataBodyRange) Is Nothing Then Exit Sub If Target.CountLarge <> 1 Then Exit Sub If LCase$(Trim$(CStr(Target.Value))) <> "discontinued" Then Exit Sub changedRow = Target.Row - activeTbl.DataBodyRange.Row + 1 patientID = activeTbl.ListColumns("Unique ID").DataBodyRange.Cells(changedRow, 1).Value Application.EnableEvents = False For i = activeTbl.ListRows.Count To 1 Step -1 If activeTbl.ListColumns("Unique ID").DataBodyRange.Cells(i, 1).Value = patientID Then archiveTbl.ListRows.Add.Range.Value = activeTbl.ListRows(i).Range.Value activeTbl.ListRows(i).Delete End If Next i SafeExit: Application.EnableEvents = True End Sub
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). In the CVL table, change one Status value to Discontinued and confirm that every CVL row with the same Unique ID appears in Archived_CVL and is removed from CVL.
Manage active and archived patient records in WPS Spreadsheet
WPS Spreadsheet provides a familiar table-based workspace for maintaining active and archived records. You can organize records by Unique ID and Status, filter discontinued patients, and move matching rows between worksheets while keeping your workbook in a widely compatible spreadsheet format.
- 1. Open the patient record workbook: Open the existing XLSX or XLSM file in WPS Spreadsheet and verify that the active and archive sheets are available.
- 2. Filter the active records: Select the active data range, then use Data > Filter to filter the Unique ID and identify all rows belonging to the patient.
- 3. Move matching records to the archive: Select the matching rows, use Ctrl+C to copy them, select the first empty row in the archive sheet, and press Ctrl+V to paste them.
- 4. Remove archived rows from the active list: After verifying the archive entries, select the original matching rows in the active sheet, right-click, and choose Delete to remove them.
Frequently Asked Questions
Why does the macro loop from the last table row to the first row?
Deleting a ListRow changes the positions of rows below it. A reverse loop, using `For i = ListRows.Count To 1 Step -1`, avoids skipped matching rows after each deletion.
Will this archive records when a patient has changed units?
Yes. The macro compares the Unique ID only, not the unit value. Every CVL row with the same Unique ID is moved to Archived_CVL regardless of the unit listed in the row.
Where should I paste the Worksheet_Change code?
Paste it into the code module for the worksheet that contains the CVL table. Right-click that worksheet tab, select View Code, and paste the procedure there.
What happens if the Status cell is changed to a value other than Discontinued?
Nothing is moved. The procedure exits unless the changed cell is in the CVL Status column and its value, ignoring capitalization and surrounding spaces, is Discontinued.
Why are Excel events disabled in the macro?
`Application.EnableEvents = False` prevents the row additions and deletions performed by the macro from triggering Worksheet_Change again. The SafeExit section turns events back on when processing finishes or an error occurs.
