VBA to find an copy rows based on contents from another cell/remove unwanted numbers and trailing spaces in multiple rows. And some easier things.

[VikingLink]

For our annual school trip to London, I'm trying to make an Excel sheet to make our life easier. So far I've managed to Google my way through most of the coding, but I'm stuck on a couple of things I want to add.

I have 3 sheets in my file on which I need some help.

1. Either create a macro button to remove unwanted numbers, parenthesis and trailing spaces or adapt my VBA code to not look for a perfect match.

On the first sheet(see above) I copy the results from a survey in which all pupils fill in their name, class, and the activities they want to do (with a backup option).
On my Overview (see below) page I have a summary of the activities we will do and the amount of pupils that will be doing them.

On another sheet I use a macro to copy the names of the pupils in separate lists per activity.

When creating these lists, I use the following VBA code:

Sub CopyNamesActivityB1()


Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range

Set StatusCol = Sheet1.Range("F2:F200")

For Each Status In StatusCol

  If Sheet5.Range("B7") = "" Then
        Set PasteCell = Sheet5.Range("B7")
    Else
         Set PasteCell = Sheet5.Range("B6").End(xlDown).Offset(1, 0)
    End If
    
       If Status = Sheet4.Range("A13") Then Status.Offset(0, -5).Resize(1, 3).Copy PasteCell
 
Next Status

Range("B6:D200").Sort Key1:=Range("D1"), _
                     Order1:=xlAscending, _
                     Key2:=Range("C1"), _
                     Order1:=xlAscending, _
                     Header:=xlYes
End Sub

This code is meant to look at the name of the activity in my Overview sheet in a specific cell (A2 for example, which looks for pupils going to Tate Modern), then check all the pupils that selected the activity with that name on the list with all the pupils, and copy said names to the list on the 3rd image. Every activity has their own macro button that checks for that specific activity. So I have a button that checks for the activity in Overview A2, one for A3, one for A4, ... (The cell references in the code don't match the examples, as I had to make a mockup due to privacy reasons)

Everything worked fine, but my survey, for some reason, adds a number, a parenthesis and a space to the name of the activity. This causes a problem when I copy that list to the first sheet as the VBA looks for an exact match.
I am looking for a way to either remove the number, parenthesis and space from the name on the first sheet, or make my VBA look for a partial match instead of an exact match. I found a partial solution on this forum, but that only worked for a single column to remove the unwanted characters. (VBA : Remove Number, Extra Spaces, Mark (.) from a Text)

2. Make cells in the top row of the pupils overview red if a name occurs twice.

Sometimes pupils fill in their survey more than once, and I want to highlight the names of pupils that occur more than once. I managed to do so via conditional formatting, but I also want the cells with First name and Name in the top row to have a coloured fill if there are one or more duplicates. I can't seem to find out how to do this.

I'm using =COUNTIFS($A$2:$A$200,$A2,$B$2:$B$200,$B2)>1 in the conditional formatting applied to =$A$2:$B$200 to highlight the duplicates themselves. But I can't get the top row to be highlighted if there is at least one duplicate.

3. Remove cancelled activities and copy the backup activity to the primary place.

I want to add a set place on my Overview sheet to fill in cancelled activities and then have a macro to remove those activities from the Pupil's sheet and copy their backup activity to the now emptied cells. That way I could rerun my activity macro to create the new list. I used Find & replace this year, but that takes too long, so I want to make a macro for it.

Example: Tate Modern is cancelled, so both Tom Hanks and Travis Fimmel who have this activity on the Pupil's sheet in column D, will now go to London Dungeon, which is in column E. I need the macro to empty the cells with Tate Modern in column D and refill that cell with whatever activity is on the same row in column E. It should do this based on what is written in the range I2:I6 on the Overview sheet. There are multiple activity moments, so I would use 1 column per time frame (Thursday morning => column I, Thursday afternoon => column J, ... )

I am a total newbie when it comes to Excel, so I've poured about a 100 hours into this Excel already (because I have to Google everything and then try and figure out what the code is doing), but for the life of me, I can't figure out how to do these final 3 things. Any help is greatly appreciated. I hope I explained this clearly enough, but do let me know if you have questions.

 

[VikingLink]

Please delete this topic, I described it far too complicated and will create separate topics.