Hey Varun,

Find below the code for all your steps above. I couldn't understand condition C. If you can explain that to me, i can have that inputted in the code as well. Test the below and let me know if you face any issues.

Sub copy_data()

Dim NewFN As String
Dim MasterFN As String

'Open the Master file
proceed:
MasterFN = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the Master File")
If MasterFN = "" Then
    MsgBox "You have not selected a file."
    GoTo proceed
Else
    Workbooks.Open Filename:=MasterFN
End If
MasterFN = ActiveWorkbook.Name

'Open the test file
proceed1:
NewFN = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please select a file")
If NewFN = "" Then
    MsgBox "You have not selected a file."
    GoTo proceed1
Else
    Workbooks.Open Filename:=NewFN
End If


'Save backup file
ActiveWorkbook.SaveAs Filename:="D:\Counts-" & Format(Date, "dd-mmm-yy") & ".xlsx", FileFormat:= _
      xlOpenXMLWorkbook, CreateBackup:=False

Workbooks("Counts-" & Format(Date, "dd-mmm-yy") & ".xlsx").Close

Workbooks.Open Filename:=NewFN
NewFN = ActiveWorkbook.Name

'Delete the "to be removed" IDs
Workbooks(NewFN).Activate
lrow = Worksheets("To be removed").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lrow

    Worksheets("Raw").Rows("1:1").AutoFilter field:=1, Criteria1:=Worksheets("To be removed").Range("A" & i).Value
    
    With ActiveSheet
        Set rngF = .AutoFilter.Range
        If rngF.Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
                    .Cells.Count = 1 Then
                GoTo cont
        End If
    
    End With

    With rngF  'ignore the header from the count and come down one row
            'On Error GoTo extra
            Set rngV = .Resize(.Rows.Count - 1, 1).Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
    End With

    rngV.Next.EntireRow.Delete

cont:
Next i

Worksheets("Raw").Rows("1:1").AutoFilter

lrow = Worksheets("Raw").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lrow

update_data:

    SName = Workbooks(NewFN).Worksheets("Raw").Range("B" & i).Value
    On Error GoTo new_tab
    Workbooks(NewFN).Worksheets("Raw").Range("A" & i & ":I" & i).Copy Workbooks(MasterFN).Worksheets(SName).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
    Workbooks(MasterFN).Worksheets(SName).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Format(Date, "dd-mmm-yy")
    drow = Workbooks(MasterFN).Worksheets(SName).Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Row
    Workbooks(MasterFN).Worksheets(SName).Range("K" & drow - 1 & ":S" & drow - 1).Copy Workbooks(MasterFN).Worksheets(SName).Range("K" & drow & ":S" & drow)

Next i

new_tab:
MsgBox "New Name encountered", vbCritical
UserA = InputBox("Should a new sheet be inserted for the new name?", "New Name", "Yes")
If UserA = "Yes" Then
    Workbooks(MasterFN).Sheets.Add after:=Workbooks(MasterFN).Sheets(Worksheets.Count)
    ActiveSheet.Name = SName
Else: UserA = "No"
    UserB = InputBox("Specify the name of the sheet where the data should be merged")
    SName = UserB
End If

    Workbooks(NewFN).Worksheets("Raw").Range("A" & i & ":I" & i).Copy Workbooks(MasterFN).Worksheets(SName).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
    Workbooks(MasterFN).Worksheets(SName).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Format(Date, "dd-mmm-yy")
    drow = Workbooks(MasterFN).Worksheets(SName).Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Row
    Workbooks(MasterFN).Worksheets(SName).Range("K" & drow - 1 & ":S" & drow - 1).Copy Workbooks(MasterFN).Worksheets(SName).Range("K" & drow & ":S" & drow)

If UserA = "" Then
    MsgBox "This work is now complete"
ElseIf UserA = "Yes" Then
    MsgBox "This work is now complete, new sheet added " & SName
ElseIf UserA = "No" Then
    MsgBox "This work is now complete, merged with sheet " & UserB
End If

End Sub
Note: In the Master file, i also found formulae from columns K to S missing in some of the tabs, i have inputted that and then run the code. So maybe you can have those formulae inputted first and then save and close your file. The macro will then ask you to open the master file.

Also, when the new tab got added for RRS, there were no headings or formulae, so it just inputted the data. Maybe once the macro runs, you can complete that part of the sheet.