Combining Multiple Spreadsheets (semi-duplicates)

Try this

Option Explicit

Sub Main()
    CopyToOneSheet
    CombineLikeDataRemoveDuplicates
    DeleteOldSheets
End Sub

Private Sub CopyToOneSheet()
    Dim ws As Worksheet
    Dim LastRow As Long, NextRow As Long

    'Add new sheet to workbook
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Combined"
    
    Set ws = ActiveSheet
    'Copy the first sheet with headers to "Combined"
    With Sheets("Data Capture 1")
        LastRow = .UsedRange.Rows.Count
        .Rows("1:" & LastRow).Copy ws.Range("A1")
    End With
    'Copy the next sheets without headers to "Combined"
    With Sheets("Data Capture 2")
        LastRow = .UsedRange.Rows.Count
        NextRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
        .Rows("2:" & LastRow).Copy ws.Range("A" & NextRow)
    End With
    With Sheets("Data Capture 3")
        LastRow = .UsedRange.Rows.Count
        NextRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
        .Rows("2:" & LastRow).Copy ws.Range("A" & NextRow)
    End With
    
    'Sort the new sheet data on column A
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2:A" & LastRow), SortOn:=xlSortOnValues, _
                        Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:L" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    ws.Range("A:L").EntireColumn.AutoFit
    
End Sub

Private Sub CombineLikeDataRemoveDuplicates()
    Dim ws As Worksheet
    Dim LastRow As Long, RowNo As Long, ColNo As Long

    Set ws = Sheets("Combined")
    With ws
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        For RowNo = LastRow To 2 Step -1
            If .Cells(RowNo, 1) = .Cells(RowNo - 1, 1) Then
                For ColNo = .Columns("B").Column To .Columns("L").Column
                    If .Cells(RowNo - 1, ColNo) = "" Then
                        .Cells(RowNo - 1, ColNo) = .Cells(RowNo, ColNo)
                    End If
                Next
                .Cells(RowNo, ColNo).EntireRow.Delete
            End If
        Next
    End With
End Sub

Private Sub DeleteOldSheets()
    On Error GoTo ResetApplication
    Application.DisplayAlerts = False
    
    Sheets("Data Capture 1").Delete
    Sheets("Data Capture 2").Delete
    Sheets("Data Capture 3").Delete
    
ResetApplication:
    Err.Clear
    On Error GoTo 0
    Application.DisplayAlerts = True
End Sub

This will copy all the sheets to a new sheet.
Duplicate entries are checked for empty cells and these are filled from the row below, if two rows have data in the same column, the first row takes precedence.
The "duplicate" row is then deleted
Finally the old worksheets are deleted.

If you need to keep the original data for any reason, including checking, use Save As to save the result, if not just save the workbook.

Try this on a copy of your real workbook first. To run the code use the macro "Main"

The code is not meant to be clever, I have tried to keep it as basic as possible, just one step up from the macro-recorder, that should help you follqw it easily and make any changes to suit your real sheet that you might need.

Hope this helps

I said in my previous Post

The code is not meant to be clever, I have tried to keep it as basic as possible......

This is no excuse for bad practice

UsedRange is unreliable and should best be avoided

Change this Line (3 Places)

        LastRow = .UsedRange.Rows.Count

To this

        LastRow = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), _
                              SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Thanks for the reminder Jerry

Apologies again