Copy data based on on specific header "name"

Copy this to a code module in source.xlsx. You'll need to save it as a .xlsm.

Option Explicit
Dim SrcRow As Long, SrcCol As Long, DestRow As Long, DestCol

Sub CopyData()
    Sheets("Working").Select
    SrcRow = Range("B1").End(xlDown).Row
    
    With Workbooks("destination.xls").Sheets("Sample")
        DestRow = .Rows.Count
        Do Until .Cells(DestRow, 1).Value = "Note1"
            DestRow = .Cells(DestRow, 1).End(xlUp).Row
            If DestRow < 2 Then
                MsgBox "Note1 row not found"
                Exit Sub
            End If
        Loop
        DestRow = DestRow - 1
        .Rows(DestRow & ":" & DestRow + SrcRow - 2).Insert
        .Rows(DestRow + SrcRow - 1).Copy
        .Rows(DestRow & ":" & DestRow + SrcRow - 2).PasteSpecial (xlPasteFormats)
        Application.CutCopyMode = False

        SrcCol = 2 ' Part Number
        Range(Cells(2, SrcCol), Cells(SrcRow, SrcCol)).Copy Destination:=.Cells(DestRow, 1)
        .Range(.Cells(DestRow, 2), .Cells(DestRow + SrcRow - 2, 2)).Formula = "=A" & DestRow & "&""-NEW"""
        
        Do Until Cells(1, SrcCol).Value = "In"
            SrcCol = SrcCol + 1
            If Cells(1, SrcCol).Value = "" Then
                MsgBox "In heading not found"
                Exit Sub
            End If
        Loop
        Range(Cells(2, SrcCol), Cells(SrcRow, SrcCol)).Copy Destination:=.Cells(DestRow, 3)
        
        Do Until Cells(1, SrcCol).Value = "Category"
            SrcCol = SrcCol + 1
            If Cells(1, SrcCol).Value = "" Then
                MsgBox "Category heading not found"
                Exit Sub
            End If
        Loop
        Range(Cells(2, SrcCol), Cells(SrcRow, SrcCol)).Copy Destination:=.Cells(DestRow, 4)
    End With
End Sub

I would need to see the entire subroutine to evaluate.

What you did with shtSource.Activate is a good solution. Here's the other way, just so the problem is clarified, but it's messier:

shtSource.Range(shtSource.Cells(2, SrcCol), shtSource.Cells(SrcRow, SrcCol)).Copy Destination:=.Cells(DestRow, 1)

An even better way might be to reverse the order in which you open the workbooks. By opening shtSource last, it would already be the active workbook. That's up to you though.

Regarding the column sequence, that is resolved by resetting the value of SrcCol back to 3 before starting the search.

You can't concatenate a value (_PASS_OK) to a range of values with a single command. You have to loop through the range and add it to each cell.

By "clear the selection", I assume you to mean that you want to end up with a single cell selected, rather than a range, in both sheets. You can only select a cell and/or range on a sheet that is the active sheet. In your last line, above, you're trying to select a cell in shtTarget, but shtSource is the active sheet.

The code below will hopefully resolve all issues mentioned. I also updated the file open procedures to allow for cancelling the open.

Option Explicit
Sub CopyDataNew()

    Dim bkSource As Workbook, bkTarget As Workbook
    Dim shtSource As Worksheet, shtTarget As Worksheet
    Dim SrcRow As Long, DestRow As Long, SrcCol As Long
    Dim strFilename As String

    strFilename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Select Source file")
    If strFilename = "False" Then
        Exit Sub
    End If
    Set bkSource = Workbooks.Open(Filename:=strFilename)

    strFilename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Select Target file")
    If strFilename = "False" Then
        bkSource.Close (False)
        Exit Sub
    End If
    Set bkTarget = Workbooks.Open(Filename:=strFilename)

    Set shtSource = bkSource.Sheets("Working")
    Set shtTarget = bkTarget.Sheets("Sample")

    Application.ScreenUpdating = False

    shtSource.Activate
    SrcRow = Range("B1").End(xlDown).Row

    With shtTarget
        DestRow = .Rows.Count
        Do Until .Cells(DestRow, 1).Value = "Note1"
            DestRow = .Cells(DestRow, 1).End(xlUp).Row
            If DestRow < 2 Then
                MsgBox "Note1 row not found"
                Exit Sub
            End If
        Loop
        DestRow = DestRow - 1
        .Rows(DestRow & ":" & DestRow + SrcRow - 2).Insert
        .Rows(DestRow + SrcRow - 1).Copy    '
        .Rows(DestRow & ":" & DestRow + SrcRow - 2).PasteSpecial (xlPasteFormats)

        SrcCol = 2 ' Part Number
        Range(Cells(2, SrcCol), Cells(SrcRow, SrcCol)).Copy Destination:=.Cells(DestRow, 1)
        .Range(.Cells(DestRow, 2), .Cells(DestRow + SrcRow - 2, 2)).Formula = "=A" & DestRow & "&""-NEW"""
        
        Do Until LCase(Cells(1, SrcCol).Value) Like "*in*" Or _
                LCase(Cells(1, SrcCol).Value) Like "*qty*" Or _
                LCase(Cells(1, SrcCol).Value) Like "*total*" Or _
                LCase(Cells(1, SrcCol).Value) Like "*sum*"
            SrcCol = SrcCol + 1
            If Cells(1, SrcCol).Value = "" Then
                MsgBox "In heading not found"
                Exit Sub
            End If
        Loop
        Range(Cells(2, SrcCol), Cells(SrcRow, SrcCol)).Copy Destination:=.Cells(DestRow, 3)

        SrcCol = 3 ' Reset to search for Category
        Do Until Cells(1, SrcCol).Value = "Category"
            SrcCol = SrcCol + 1
            If Cells(1, SrcCol).Value = "" Then
                MsgBox "Category heading not found"
                Exit Sub
            End If
        Loop
        Range(Cells(2, SrcCol), Cells(SrcRow, SrcCol)).Copy
        .Cells(DestRow, 4).PasteSpecial xlPasteValues
        For DestRow = DestRow To SrcRow - 2
            .Cells(DestRow, 4).Value = .Cells(DestRow, 4).Value & "_PASS_OK"
        Next
        Range("A1").Select ' in shtSource
        .Activate
        Range("A1").Select ' in shtTarget
    End With
    
    Application.CutCopyMode = False
End Sub

Just something extras to make the code even better.

Sometimes, the source file comes with an additional column "Class". I want to add a check for this heading. If found, the whole range from source file under Class heading is copied to column 5 in target file. If the heading is not found, it'll skip the copy. I use Goto command.

        Do Until LCase(Cells(1, SrcCol).Value) Like "*class*"
            SrcCol = SrcCol + 1
            If Cells(1, SrcCol).Value = "" Then GoTo Cleanup
        Loop
        Range(Cells(2, SrcCol), Cells(SrcRow, SrcCol)).Copy Destination:=.Cells(DestRow, 5)

In shtTarget the column 2 "New Name" shows "100A21-NEW" but it actually stores a formula e.g =A12&"-NEW". To change them to value instead of formula. I added this code below the .formula line to reselect the range, copy, then paste as value only.

      .Range(.Cells(DestRow, 2), .Cells(DestRow + SrcRow - 2, 2)).Formula = "=A" & DestRow & "&""-NEW"""
       For DestRow = DestRow To DestRow + SrcRow - 2
            .Cells(DestRow, 2).Copy
            .Cells(DestRow, 2).PasteSpecial xlPasteValues
        Next

Lastly I want to make sure the column 3 "Qty Count" is rounded and has no decimal point. If value is 20.1, save it as 20. If value is 20.9, save it as 21. Again, I use the sample you make for adding "_PASS_OK" .

For DestRow = DestRow To DestRow + SrcRow - 2
            .Cells(DestRow, 3).Value = Round(.Cells(DestRow, 3).Value, 0)
        Next

Is there a better way to make the code more efficient?

Looks like you're handling it pretty well.