Macro to correct entries across multiple sheets

[Lux Aeterna]

Hello everyone,

I need help creating a VBA macro to correct misspelled names across multiple sheets in my Excel workbook.

Workbook Setup

1. Crosscheck Sheet: This sheet lists discrepancies between patient names and their Unique IDs across several other sheets (referred to as "List sheets").
Here's the column layout of the Crosscheck sheet:

• Column A: List sheet name (e.g., "List2022", "List2023", etc.)
• Column B: Patient's Dynamic visit number (this changes with each visit and is not a unique identifier)
• Column C: Name (the patient’s name)
• Column D: Unique ID (this is the actual unique identifier for each patient)
• Column G: Checkbox (indicating whether the name in this row is incorrect)

2. List Sheets: I have multiple List sheets (e.g., "List2022", "List2023", etc.), each containing:

• Column A: Patient's Dynamic visit number
• Column C: Patient names

These sheets are locked with a password, which is "299"

What I need the macro to do

1. Identify Groups: In the Crosscheck sheet, the macro needs to group entries based on the Unique ID in Column D. Each group may have multiple entries referring to the same Unique ID but with different names (e.g., due to typos or mistakes).

2. Find Correct Name: For each group:

• The unticked entry in Column G contains the correct patient name.
• The ticked entries contain incorrect names.

3. Correct Entries in List Sheets: For each ticked (incorrect) entry:

• The macro should find the corresponding patient in the relevant List sheet (based on Column A of Crosscheck).
• It should locate the row in the List sheet by matching the Visit number in Column B (from Crosscheck) to Column A in the relevant List sheet.
• Once located, the macro should replace the incorrect name (Column C in the List sheet) with the correct name (from the unticked entry in Crosscheck).

4. Add a Comment: After updating the name in the List sheet, the macro should add a comment to the corrected cell in Column C, saying:
"Previous entry: [incorrect name] - Corrected on: [current date dd-mm-yyyyy]"

• If there's already a comment in the cell, the macro should update the existing comment by appending the new information, rather than replacing the existing comment.

5. Protected Sheets: The List sheets are protected with the password "299", so the macro needs to unprotect each sheet before making changes and reprotect it afterward.

6. Rename the corresponding file: The file is located in C:\Users\pc50\Documents\Results\yyyy\, where yyyy represents the year indicated by the List sheet. For example, if an entry is from List2023, yyyy should be 2023.

Additional information

• There may be more than one correct entry (unticked checkboxes) within a group.
• Some groups may not contain any ticked entries, and these should be ignored.
• The macro should only update entries when there’s at least one ticked and one unticked entry in a group.

​

Example​

Let’s assume the following data in Crosscheck:

Column A	Column B (Dynamic visit number)	Column C	Column D (unique ID)	Column G	This explanation about selected/not selected is for clarification purposes only and will not appear in any cell.
List2022	4655	Kevin	53111	checkbox that is selected
List2023	562	Kevin	53111	checkbox that is selected
List2024	7290	Kim	53111	checkbox that is not selected	correct entry for unique ID 53111
List2022	7342	Mary	27555	checkbox that is not selected	correct entry for unique ID 27555
List2024	1	Matias	27555	checkbox that is selected

• Group 1 (Unique ID 53111): There are two incorrect entries (Kevin) and one correct entry (Kim). The macro should:

• Go to List2022, find the row where the Dynamic visit number 4655 is (it is located in column A), replace the name Kevin with Kim (in column C), and add a comment (in column C): "Previous entry: Kim. Corrected on: 31/12/2024"
  • Rename the file located at C:\Users\pc50\Documents\Results\2022\4655 Kevin.pdf to 4655 Kim.pdf
• Go to List2023, find the row where the Dynamic visit number 562 is, replace the name Kevin with Kim, and add a comment: "Previous entry: Kim. Corrected on: 31/12/2024"
  • Rename the file located at C:\Users\pc50\Documents\Results\2023\562 Kevin.pdf to 562 Kim.pdf

• Group 2 (Unique ID 27555): There is one incorrect entry (Matias) and one correct entry (Mary). The macro should:
  • Go to List2024, find the row where the Dynamic visit number 1 is, replace the name Matias with Mary , and add a comment: "Previous entry: Matias. Corrected on: 31/12/2024"
    • Rename the file located at C:\Users\pc50\Documents\Results\2024\1 Matias.pdf to 1 Mary.pdf

Thank you in advance!!