Nice title ! ?? **
Try this..
Its all in the reference sheet code module
[VBA]Option Explicit
Sub ExtractSumCode()
'Purpose: ExtractCustCode() procedure extracts the related Customer code from
'the Reference worksheet tables using IF conditions and VLOOKUP functions.
Dim i As Integer
Dim RCount As Long
Dim SumCode As Range, TestTable As Range
Dim cell As Range
Call Worksheets("Reference").Range1Update
With Worksheets("AR_Aging")
RCount = .Range("A65536").End(xlUp).Row
With .Range("M2:M" & Trim(Str(RCount)))
.Formula = "=VlookUp(A2,Range1,3,false)" 'false ensure no xl approx match
.Name = "SumCode"
End With
With .Range("M1") ' Presumed P1 was supposed to be M1 ?
.Value = "SumCode"
.Font.Bold = True
End With
End With
End Sub
Sub Range1Update()
'This routine updates the range "Range1" to provide an effective table for customer SumCode
ActiveWorkbook.Names("Range1").Delete
With Worksheets("Reference").Range("D1")
Range(.Offset(0, 0), .Offset(0, 2).End(xlDown)).Name = "Range1"
End With
With Worksheets("Reference").Range("D1")
.Sort Key1:=Range("Range1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers, _
DataOption2:=xlSortNormal
End With
End Sub
[/VBA]