Copy cell values from multiplesht to single sheet as format cells

Start by unmerging the range containing the text "MANUFACT COUNTRY" in each of the 3 sheets. Then try the macro below. You should avoid using merged cells at all cost because they create serious problems for macros. Do a little research into "CenterAcrossSelection". This has the same visual effect as merging horizontal cells without actually merging them.

Sub CopyVals()
    Application.ScreenUpdating = False
    Dim lRow As Long, ws As Worksheet, desWS As Worksheet, CC As Range, MC As Range, BC As Range
    Set desWS = Sheets("Expected Result")
    For Each ws In Sheets
        If ws.Name <> "Expected Result" Then
            With ws
                Set CC = .Range("A:A").Find("CAR CODE")
                Set MC = .Range("A:A").Find("MANUFACT COUNTRY")
                Set BC = .Range("A:A").Find("BAR CODE NO")
                With desWS
                    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                    .Range("A" & lRow) = CC.Offset(, 1)
                    .Range("B" & lRow) = MC.Offset(1)
                    .Range("C" & lRow) = BC.Offset(1)
                End With
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Please attach a copy of the file that is generating the error.

In the first 3 sheets, you have merged cells in row 25 from A:K. Unmerge those cells before trying the macro below. You should avoid using merged cells because they almost always create problems for macros.

Sub CopyVals()
    Application.ScreenUpdating = False
    Dim lRow As Long, ws As Worksheet, desWS As Worksheet, CC As Range, MC As Range, BC As Range
    Set desWS = Sheets("Expected Result")
    For Each ws In Sheets
        If ws.Name <> "Expected Result" Then
            With ws
                Set CC = .Range("A:A").Find("CAR CODE")
                Set MC = .Range("A:A").Find("MANUFACT COUNTRY")
                Set BC = .Range("A:A").Find("BAR CODE NO")
                With desWS
                    lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                    .Range("A" & lRow) = CC.Offset(, 1)
                    .Range("B" & lRow) = MC.Offset(1)
                    .Range("C" & lRow) = BC.Offset(1)
                End With
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub