Auto copy specific data from sheet to antoher sheet under few condition

Current, VBA code manage to copy data from sheet [Template] to sheet [All] when run the macros.

My issues are:-

[refer to file attached]
1. How to auto run the macros when re-select drop down list at column C4 or before save the file? And there is duplicate data copy over to sheet [All]

2. How to ensure all data from sheet [Template] to sheet [All] doesn't over-copy? Result wanted may refer to sheet [Example].

Sub Search_Copy()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim HeaderCells As Range, Hdr As Range, hdrFIND As Range

Application.ScreenUpdating = False                          'speed things up
Set ws1 = Sheets("Template")                 'Define name of worksheets
Set ws2 = Sheets("All")

With ws1
    'Set HeaderCells = .Rows(7).SpecialCells(xlConstants)    'a collection of the headers on ws1
    
    Set HeaderCells = .Range("A7", .Cells(7, .Columns.Count).End(xlToLeft))

    On Error Resume Next                                    'skip columns where header isn't found on ws2
    For Each Hdr In HeaderCells                             'process one header cell at a time
        Set hdrFIND = ws2.Rows(1).Find(Hdr.Text, LookIn:=xlValues, LookAt:=xlWhole)     'try to find match on ws2
        If Not hdrFIND Is Nothing Then                      'if found, copy to that column on ws2
            .Range(Hdr.Offset(1), .Cells(.Rows.Count, Hdr.Column)).Copy ws2.Cells(Rows.Count, hdrFIND.Column).End(xlUp).Offset(1)
        End If
    Next Hdr
End With

Application.ScreenUpdating = True                           'back to normal
End Sub

Originally Posted by Tinbendr

Just add macro name to Worksheet_Change event.


Create a sub named Save in a standard module. This will intercept the standard Save routine. But you'll be responsible for actually saving the document.

If you want to add new data to the data already there, then use Ron's Last Cell routine to determine last row that has data.

Attached is latest file.

Combined 2 macros together.
The Problems are:-

1. I not sure how to apply Last Cell routine into fie
2. Current macros sheettosheet is able to copy the data. Any simplfy code as actaully file have long list of header to copy?
3. How to ensure the copy over is only the data while the blank one doesn't copy over? because once click the macros, the file size become huge.
4. How to active sheettosheet macros before save the file?

Thanks