Sub PrepaTCD()

Application.ScreenUpdating = False

'   Macro d'importation d'un fichier dans le classeur ouvert.
    TabName1 = "Paramètres"
    ActiveSheet.Name = TabName1
    
' Assignation de valeurs aux différentes variables et paramètres
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Compte collectif client"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "'410000"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Compte de TVA"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "'445906"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Management fees"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "'707200213100"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "CA L 'shi - Frontière - Export"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "'7062002141??"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "CA Frontière - Ndola - Export"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "'7062002131??"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "CA L 'shi - Frontière - Import"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "'7061002141??"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "CA Frontière - Ndola - Import"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "'7061002131??"

    
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "Chemin des données WinBooks"
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "C:\WINBOOKS\DATA\HK\"
    Range("A11").Select
    ActiveCell.FormulaR1C1 = "Fichier des transactions comptables"
    Range("B11").Select
    ActiveCell.FormulaR1C1 = "HK_ACT.DBF"
    Range("A12").Select
    ActiveCell.FormulaR1C1 = "Fichier des transactions analytiques"
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "HK_ANT.DBF"
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "Nom de l'onglet ACT"
    Range("B13").Select
    ActiveCell.FormulaR1C1 = "HK_ACT"
    Range("A14").Select
    ActiveCell.FormulaR1C1 = "Nom de l'onglet ANT"
    Range("B14").Select
    ActiveCell.FormulaR1C1 = "HK_ANT"
    
    Columns("A:B").EntireColumn.AutoFit

' Valorisation des variables
    PathName = Range("B10").Value
    ACT = Range("B11").Value
    ANT = Range("B12").Value
    TabName2 = Range("B13").Value
    TabName3 = Range("B14").Value
    
' Import du fichier ACT
    ControlFile = ActiveWorkbook.Name
    Workbooks.Open Filename:=PathName & ACT
    ActiveSheet.Name = TabName2
    Sheets(TabName2).Copy After:=Workbooks(ControlFile).Sheets(1)
    Windows(ACT).Activate
    ActiveWorkbook.Close SaveChanges:=False
    Windows(ControlFile).Activate
    
' Import du fichier ANT
    ControlFile = ActiveWorkbook.Name
    Workbooks.Open Filename:=PathName & ANT
    ActiveSheet.Name = TabName3
    Sheets(TabName3).Copy After:=Workbooks(ControlFile).Sheets(2)
    Windows(ANT).Activate
    ActiveWorkbook.Close SaveChanges:=False
    Windows(ControlFile).Activate

' Première macro de préparation des données à l'affichage en tableau croisé dynamique
'
    
' Selection de la feuille ACT
    Sheets(TabName2).Select
    
' Sélection de toutes les colonnes et mise en place du filtre
    Columns("A:AL").Select
    Selection.AutoFilter
    
' Effacement de toutes les opérations qui ne sont pas des factures ou des notes de crédit sur ventes.
    ActiveSheet.Range("A:AL").AutoFilter Field:=3, Criteria1:=Array( _
        "0", "1", "4", "5"), Operator:=xlFilterValues
    Range("A1").Select
    ActiveCell.Offset(1, x).Select
    Rows(ActiveCell.Row).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("A:AL").AutoFilter Field:=3

' Sélection et effacement des données de la colonne 8, c'est-à-dire Accountrp, dans ce cas-ci; les clients.
' Effacement de tous les clients autres que TFM.
    ActiveSheet.Range("A:AL").AutoFilter Field:=8, Criteria1:= _
        "=LOCATAIRE", Operator:=xlOr, Criteria2:="=TFM DIVERS"
    Range("A1").Select
    ActiveCell.Offset(1, x).Select
    Rows(ActiveCell.Row).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("A:AL").AutoFilter Field:=8

' Sélection et effacement des données de la colonne 9, c'est-à-dire Bookyear, dans ce cas-ci; l'année fiscale.
' Effacement de l'année 1, c'est-à-dire 2013.
    ActiveSheet.Range("A:AL").AutoFilter Field:=9, Criteria1:="1"
    Range("A1").Select
    ActiveCell.Offset(1, x).Select
    Rows(ActiveCell.Row).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("A:AL").AutoFilter Field:=9

'Effacement des colonnes inutiles
    Range( _
        "A:A,C:C,H:H,K:K,M:M,P:P,T:T,U:U,V:V,Y:Y,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AF:AF,AG:AG,AH:AH,AI:AI,AJ:AJ,AK:AK,AL:AL" _
        ).Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select

' Selection de la feuille ANT
    Sheets(TabName3).Select
    
' Activation du filtre sur tous les champs.
    Columns("A:Z").Select
    Selection.AutoFilter
    
' Effacement de toutes les opérations qui ne sont pas des factures ou des notes de crédit sur ventes.
    ActiveSheet.Range("A:Z").AutoFilter Field:=2, Criteria1:=Array( _
        "0", "1", "4", "5", "7"), Operator:=xlFilterValues
    Range("A1").Select
    ActiveCell.Offset(1, x).Select
    Rows(ActiveCell.Row).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("A:AL").AutoFilter Field:=2


' Selection de la feuille ACT
    Sheets(TabName2).Select
    
'Insertion de 15 colonnes pour recevoir les données qui seront traitées.
    Columns("A:O").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveSheet.Cells(1, 1).Value = "Jnl_NoDoc"
    ActiveSheet.Cells(1, 2).Value = "Jnl_NoDoc_CptGen_DocOrder"
    ActiveSheet.Cells(1, 3).Value = "Jnl_NoDoc_CptGen_CodeTVA"
    ActiveSheet.Cells(1, 4).Value = "Année"
    ActiveSheet.Cells(1, 5).Value = "Mois"
    ActiveSheet.Cells(1, 6).Value = "jour"
    ActiveSheet.Cells(1, 7).Value = "Manifeste"
    ActiveSheet.Cells(1, 8).Value = "N° de facture, jour et camion"
    ActiveSheet.Cells(1, 9).Value = "CA Total"
    ActiveSheet.Cells(1, 10).Value = "CA L'shi - frontière - L'shi"
    ActiveSheet.Cells(1, 11).Value = "CA Frontière - Ndola - Export"
    ActiveSheet.Cells(1, 12).Value = "CA des fees"
    ActiveSheet.Cells(1, 13).Value = "Montant de TVA"
    ActiveSheet.Cells(1, 14).Value = "Tonnage"
    ActiveSheet.Cells(1, 15).Value = "Prestation"

' Valoristation des variables de chaque colonne
    ActiveSheet.Cells(2, 1).Formula = "=P2&Q2"
    ActiveSheet.Cells(2, 2).Formula = "=A2&T2&R2"
    ActiveSheet.Cells(2, 3).Formula = "=P2&Q2&T2&AD2"
    ActiveSheet.Cells(2, 4).Formula = "=ANNEE(W2)"
    ActiveSheet.Cells(2, 5).Formula = [=DROITE("00"&MOIS(W2);2)]
    ActiveSheet.Cells(2, 6).Formula = [=DROITE("00"&JOUR(W2);2)]
    ActiveSheet.Cells(2, 7).Formula = ""
    ActiveSheet.Cells(2, 8).Formula = ""
    ActiveSheet.Cells(2, 9).Formula = ""
    ActiveSheet.Cells(2, 10).Formula = ""
    ActiveSheet.Cells(2, 11).Formula = ""
    ActiveSheet.Cells(2, 12).Formula = ""
    ActiveSheet.Cells(2, 13).Formula = ""
    ActiveSheet.Cells(2, 14).Formula = ""
    ActiveSheet.Cells(2, 15).Formula = ""

Application.ScreenUpdating = True

End Sub