XL 2013 Importation d'un classeur fermé et classement

Str_Search = IIf(Tablo(L, 3) <> "", UCase(Left(Tablo(L, 2), 2) & Left(Tablo(L, 3), 2)), "")

Str_Compare = UCase(Left(Tablo(LL, 2), 2) & Left(Tablo(LL, 3), 2))

 Str_Compare Like Str_Search

Option Explicit
Option Base 1
Public Tablo
Public Tab_Ref() As Variant
Public Str_Search As String
Public Str_Compare As String
Public DerLgn As Long
Public DerCol As Byte
Public LL As Long
Public L As Long
Public I As Long
Public x As Long
Public tbl As Range, ShB As Worksheet
Public derlig As Long
Public Coll_Str As Collection
Public wkSource As Workbook, wkDest As Workbook, ShA As Worksheet
Public shC As Worksheet, Fichier As String

Sub Importer()
    Application.ScreenUpdating = False
    Fichier = ThisWorkbook.Path & "\INVENTAIRES.xlsx"
    Set wkSource = Workbooks.Open(Fichier)
    Set wkDest = ThisWorkbook   
    Set ShA = wkSource.Sheets("INV")   
    Set ShB = wkDest.Sheets("BD ARTICLES")
    Set shC = wkDest.Sheets(3)
With ShB

     With .Range("A7").Resize(10000, 5) 'avec la plage ainsi definie
          .ClearContents 'on efface les donnees
          .Borders.LineStyle = xlNone 'on efface les bordures
     End With
End With
    derlig = shC.Range("f" & Rows.Count).End(xlUp).Row
    With ShA
        derlig = .Range("b" & Rows.Count).End(xlUp).Row
      With .Range("A24:E" & derlig)
           .Copy ShB.Range("A7")
        Tablo = .Value
      End With
       
        .Range("b24:b" & derlig).Copy shC.Range("b7")
        .Range("f24:f" & derlig).Copy shC.Range("d7")
        .Range("h24:h" & derlig).Copy shC.Range("e7")
    End With
    shC.Range("e7:e" & derlig).Value = shC.Range("e7:e" & derlig).Value
    wkSource.Close False

    Call Codes '------------------------

    Application.DisplayAlerts = False
    ThisWorkbook.Save
End Sub

Public Sub Codes()
Erase Tab_Ref
Set Coll_Str = New Collection
I = 1
Set ShB = Sheets("BD ARTICLES")
ReDim Tab_Ref(UBound(Tablo, 1), 1)
On Error Resume Next 'gestion des erreurs eventuelles
For L = I To UBound(Tablo, 1) 'pour chaque ligne du tableau des donnees
 Str_Search = IIf(Tablo(L, 3) <> "", UCase(Left(Tablo(L, 2), 2) & Left(Tablo(L, 3), 2)), "") 'on récupére si la Colonne 3 n'est pas vide les deux premiers caracteres des Colonne "B"(2) et "C"(3)
    Coll_Str.Add Str_Search, CStr(Str_Search) 'on entre la variable ainsi définie dans la Collection des références
 If Err.Number = 0 Then 'Si réference Non encore référencée
        x = 1 'on incremente la variable
 For LL = I To UBound(Tablo, 1)  'puis pour chaque ligne du tableau
   Str_Compare = UCase(Left(Tablo(LL, 2), 2) & Left(Tablo(LL, 3), 2)) 'on va récupérer les deux premiers caracteres des Colonne "B"(2) et "C"(3)
   If Str_Compare Like Str_Search Then 'on compare les deux variables si Ok
      Tab_Ref(LL, 1) = Str_Compare & Format(x, "-0000") 'Récupére dans le tableau des références la Variable formatee
        x = x + 1: I = I + 1: L = I  'on incremente les Variables
   End If
 Next LL
 End If
Err.Clear 'on efface l'erreur eventuelle
Next L
     With Sheets("BD ARTICLES").Range("A7") 'a partir de la cellule "A7"
                               .Resize(UBound(Tab_Ref, 1), 1) = Tab_Ref 'on redimensionne la plage Cible des références
                              With .Resize(UBound(Tab_Ref, 1), 5).Borders 'puis on redimensionne la plage x Lignes 5 colonnes
                                   .LineStyle = xlContinuous 'on formate les bordures
                                   .Weight = xlThin 'Idem
                              End With
     End With
Set Tablo = Nothing: Set Coll_Str = Nothing
End Sub