Macro to compare sheetname from one workbook with another and take actions depending if there is a match or not.

Sub ExtractToSheets()
    Dim ws As Worksheet, wsNew As Worksheet
    Dim rData As Range, rCl As Range
    Dim sNm As String
   Const iCol As Integer = 3 ''///- currently uses Column 3
    
    Set ws = Sheet1    '/// you may need to change this
    
    '///extract a list of unique names
    '///first clear existing list
    With ws
        Set rData = .Range("A1").CurrentRegion
        .Columns(.Columns.Count).Clear
        rData.Columns(iCol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True
        
        For Each rCl In .Cells(1, .Columns.Count).CurrentRegion
            sNm = rCl.Text
            '///add new sheet (only if required-NB uses UDF below)
            If WksExists(sNm) Then
                '///so clear contents
                Sheets(sNm).Cells.Clear
            Else
                '///new sheet required
                Set wsNew = Sheets.Add
                With wsNew
                    .Name = sNm
                    '///AutoFilter & copy to relevant sheet
                    rData.AutoFilter Field:=iCol, Criteria1:=sNm ''/// currently filters column 3
                    rData.Copy Destination:=.Cells(1, 1)
                    .Move
                    '/// saves to a folder in the master workbook's directory
                    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\New Folder Name" & sNm, _
                    FileFormat:=51, CreateBackup:=False
                    ''///close and save new workbook
                    ActiveWorkbook.Close True
                End With
            End If
        Next rCl
    End With
    '\\\tidy up
    ws.Columns(Columns.Count).ClearContents        'remove temporary list
    rData.AutoFilter        'switch off AutoFilter
End Sub


Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function