Sub My Macro() ' NICK - this will only work if called My_Macro

Application.ScreenUpdating = False ' NICK - turn off screen updating
Application.Calculation = xlCalculationManual ' NICK - turn calculation to manual
Application.EnableEvents = False ' NICK - turn off events that may otherwise trigger during the macro

'------------------
' NICK - running the 'CompareNCopy' sub
With ThisWorkbook.Worksheets("Sheet2") 'I will check here if sheet name is different
Call CompareNCopy(.Range("B7"), .Range("H7"), .Range("N7")) 'For T-1 I will provide my own range
Call CompareNCopy(.Range("H7"), .Range("B7"), .Range("T7")) 'For T-2
End With
'=================

' NICK - turn back the things we turned off
Application.EnableEvents = True 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
' NICK - this is the meat of the program
Sub CompareNCopy(rngOrg As Range, rngFrom As Range, rngTarget As Range) ' NICK - takes 3 arguments: Org, From and Target

' NICK - dimension the variables
Dim rngCell As Range
Dim intMatch As Integer
 
' NICK - this line isn't needed
rngTarget.CurrentRegion.Clear 'This will clear the target range

' NICK - these 2 lines could be replaced with:  rngTarget.value = rngOrg.value
' It's just setting the target equal to the source
rngOrg.CurrentRegion.Copy
rngTarget.PasteSpecial xlPasteAll
 
' NICK - loop through the values in the From column
For Each rngCell In rngFrom.CurrentRegion.Columns(1).Cells
 
' NICK - initialise intMatch to 0. We'll use this later if we don't find a match
intMatch = 0
 
' NICK - look for a match
On Error Resume Next
' NICK - intMatch will either be a number or there will be an error in the VBA code.. we need the On Error Resume Next or VBA will break

intMatch = Application.WorksheetFunction.Match(rngCell.Value, rngOrg.CurrentRegion.Columns(1).Cells, 0)
On Error GoTo 0
' NICK - turn VBA error handling back on

' NICK - if we don't find an exact match
If intMatch = 0 Then
'rngCell.Resize(, rngFrom.CurrentRegion.Columns.Count).Copy

' NICK - Copy and paste under the target
rngCell.Resize(, 1).Copy
 
rngTarget.End(xlDown).Offset(1).PasteSpecial xlPasteAll
End If
Next rngCell
 
' NICK - Turn off copy mode
Application.CutCopyMode = False
 
' NICK - Sort the data around Target 
rngTarget.CurrentRegion.Sort Key1:=rngTarget, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
 
' NICK - Clean up
Set rngCell = Nothing
End Sub