Macro pr renommer des codes sur un planning

Option Explicit
Sub TheRecoder()
Dim WSSource As Worksheet, WSCible As Worksheet
Dim RangeSource As Range, CellSource As Range
Dim RangeCible As Range, CellCible As Range
 
Set WSSource = ThisWorkbook.Worksheets("Codes")
Set WSCible = ThisWorkbook.Worksheets("Planning")
With WSSource
    Set RangeSource = Range(.Range("A3"), .Range("A5000").End(xlUp))
End With
With WSCible
    Set RangeCible = Range(.Range("B6"), .Range("AF40")) [COLOR=darkgreen]'A Ajuster à la taille du planning[/COLOR]
End With
 
For Each CellCible In RangeCible
    For Each CellSource In RangeSource
            If CellCible = CellSource Then
                CellCible = CellSource.Offset(0, 1)
            Exit For
            End If
    Next CellSource
Next CellCible
 
End Sub

Sub Macro1()
Dim cel As Range 'déclare la variable cel (CELlule)
Dim pl As Range 'déclare la variable pl (PLage)
Dim r As Range 'déclare la variable r (Recherche)
 
With Sheets("Codes") 'prend en compte l'onglet "Codes"
    Set pl = .Range("A3:B" & .Range("B65536").End(xlUp).Row) 'définit la plage pl
End With 'fin de la prise en compte de l'onglet "Codes"
For Each cel In Sheets("Planning").Range("B6:AF40") 'boucle sur toutes les cellules cel de la plage B6:AF40
    Set r = pl.Find(cel.Value, , xlValues, xlWhole) 'définit la variable r (Recherche la cellule dans la plage pl)
    'si il existe au moins une occurrence de r dans pl, la cellule cel prend la valeur de la cellule à droite de r
    If Not r Is Nothing Then cel.Value = r.Offset(0, 1).Value
Next cel
End Sub

Sub Recoder()
    Application.ScreenUpdating = False
    Dim Tabl()
    Sheets("Codes").Activate
    A = Range("A3:B59").Value
    For i = 1 To UBound(A)
        ReDim Preserve Tabl(1 To UBound(A), 2)
        Tabl(i, 1) = A(i, 1)
        Tabl(i, 2) = A(i, 2)
    Next i
    Sheets("Planning").Activate
    Range("B6:AF40").Select
    For Each Cell In Selection
        For j = 1 To UBound(A)
            If Cell.Value = Tabl(j, 1) Then Cell.Value = Tabl(j, 2):Exit For
        Next j
    Next Cell
    [A1].Select
End Sub

Sub Recoder()
    Application.ScreenUpdating = False
    Dim Tabl2(): Dim B(): Dim Tabl3()
    Sheets("Codes").Activate
    A = Range("A3:B59").Value
    For I = 1 To UBound(A)
        ReDim Preserve Tabl2(1 To UBound(A), 2)
        Tabl2(I, 1) = A(I, 1)
        Tabl2(I, 2) = A(I, 2)
    Next I
    Sheets("Planning").Activate
    Range("B6:AF40").Select
    B = Selection.Value
    NRow = Selection.Rows.Count
    NCol = Selection.Columns.Count
    For K = 1 To NRow
        For I = 1 To NCol
            ReDim Preserve Tabl3(1 To NRow, 1 To NCol)
            Tabl3(K, I) = B(K, I)
            For G = 1 To UBound(A)
                If Tabl3(K, I) = A(G, 1) Then Tabl3(K, I) = A(G, 2): Exit For
            Next G
        Next I
    Next K
    Sheets("Planning").Activate
    Range("B6").Resize(UBound(Tabl3, 1), UBound(Tabl3, 2)) = Tabl3
    [A1].Select
End Sub

 Range("B6:AF40").Select

Range("B6").Select
With Selection.CurrentRegion
    Intersect(.Cells, .Offset(1, 1)).Select
End With

    Dim A As Variant
    Dim i As Integer
    Dim K As Integer
    Dim g As Integer
    Dim NRow As Integer
    Dim NCol As Integer

Sub Recoder2()
Dim Tabtemp As Variant
Dim Rng As Range
Dim L As Long
Application.ScreenUpdating = False
With Worksheets("Codes")
   Tabtemp = .Range(.Cells(3, 1), .Cells(.Cells(.Cells.Rows.Count, 1) _
                                                      .End(xlUp).Row, 2)).Value
End With
With Worksheets("Planning")
     With .Range("B6").CurrentRegion
   Set Rng = Intersect(.Cells, .Offset(1, 1))
     End With
End With
For L = 1 To UBound(Tabtemp, 1)
   Rng.Replace What:=Tabtemp(L, 1), Replacement:=Tabtemp(L, 2), _
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,  _
          SearchFormat:=False, _
   ReplaceFormat:=True
Next
Erase Tabtemp
Application.ScreenUpdating = True
End Sub