Automatisation d'un VBA selon une feuille paramétré

Bonjour à tous et merci d'avance de me lire :-)

J'ai un fichier qui intègre une macro afin de transformer des écritures de Gestion commercial en écriture comptable

Actuellement j'ai tapé moi même les données dans le VBA et est mis la saisie dans un onglet "Paramètres" l'inconvenant c'est que chaque dossier est a personnaliser et je me retrouve a devoir faire manuellement chaque changement pour chaque collaborateurs, pour chaque demandes.... enfin je vous passe les détails vous l'aurez compris.

Mon code est le suivant (ci-dessous), et j'aimerai l'automatiser avec mon onglet "Paramètres" pour que soit repris automatiquement les éléments suivant :

- Définition des variables de l'onglet Ecritures (aussi bien le libellé si possible que le N° de compte)

- Définition code de TVA (aussi bien le libellé si possible que le N° de compte)

Sub Creations_Ecritures()
'
' Creations_Ecritures Macro
Sheets("Export").Select
Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yyyy"

Dim nbre_lig As Integer
Application.ScreenUpdating = False
Sheets("Ecritures").Range("A2:Z5000").ClearContents
Sheets("Ecritures").Select
Selection.Delete Shift:=xlUp

Range("A2").Select

ligne_active_ori = 2 '1ère ligne du fichier export à traiter
'définition des colonnes du fichier exporté
nbre_col_exp = 11

col_nfact = 1
col_date = 2
col_lib = 3
col_ht = 4
col_ht55 = 5
col_tva55 = 6
col_ht10 = 7
col_tva10 = 8
col_ht20 = 9
col_tva20 = 10
col_ttc = 11

'définition des variables de l'onglet Ecritures
code_jnal = "09"
cpte_ht55 = "70420000"
cpte_ht10 = "70460000"
cpte_ht20 = "70450000"
cpte_ht0 = "70470000"
cpte_tva55 = "44571210"
cpte_tva10 = "44571610"
cpte_tva20 = "44571510"
cpte_tva0 = "44571900"
cpte_ttc0 = "411100"
cpte_ttc55 = "411200"
cpte_ttc10 = "411600"
cpte_ttc20 = "411500"

' 1ère ligne de l'onglet Ecritures
Sheets("Ecritures").Cells(2, 1).Value = "Journal"
Sheets("Ecritures").Cells(2, 2).Value = "Date"
Sheets("Ecritures").Cells(2, 3).Value = "Compte"
Sheets("Ecritures").Cells(2, 4).Value = "Pièce"
Sheets("Ecritures").Cells(2, 5).Value = "Lib mvt"
Sheets("Ecritures").Cells(2, 6).Value = "Lib pièce"
Sheets("Ecritures").Cells(2, 7).Value = "Débit"
Sheets("Ecritures").Cells(2, 8).Value = "Crédit"
Sheets("Ecritures").Cells(2, 9).Value = "Code TVA"

'Définition du format pour la colonne Journal et Date

Columns("A").Select
Selection.NumberFormat = "@" 'format texte
Columns("B").Select
Selection.NumberFormat = "dd/mm/yyyy" 'format date

ligne_active_dest = 3

While Sheets("Export").Cells(ligne_active_ori, 2).Value <> ""

If Left(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 3) = "FAC" Then
    ' test si montant 5.5% renseignés
    If Sheets("Export").Cells(ligne_active_ori, col_ht55).Value <> "" Then
    'Ecriture ligne TTC 5.5

Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value

Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_ttc55
Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)

Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value
Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value

Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = 0
Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = Sheets("Export").Cells(ligne_active_ori, col_ht55).Value + Sheets("Export").Cells(ligne_active_ori, col_tva55).Value
Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = ""

ligne_active_dest = ligne_active_dest + 1

'Ecriture lign HT 5.5
  Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
    Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_ht55
  Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)
    Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value
      Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value

    Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = 0
    Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = Sheets("Export").Cells(ligne_active_ori, col_ht55).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = "E2"

    ligne_active_dest = ligne_active_dest + 1

  'ecriture ligne tva à 5.5%
    Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
    Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value
     Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_tva55
     Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)

     Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value

    Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = 0
    Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = Sheets("Export").Cells(ligne_active_ori, col_tva55).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = "E2"
    ligne_active_dest = ligne_active_dest + 1

End If
'*********
' test si montant 10% renseignés
    If Sheets("Export").Cells(ligne_active_ori, col_ht10).Value <> "" Then
    'Ecriture ligne TTC 10

Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value

Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_ttc10
  Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)

Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value
Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value
Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = 0
Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = Sheets("Export").Cells(ligne_active_ori, col_ht10).Value + Sheets("Export").Cells(ligne_active_ori, col_tva10).Value
Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = ""

ligne_active_dest = ligne_active_dest + 1

'Ecriture lign HT 10%
  Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
    Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_ht10
     Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)
    Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = 0
    Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = Sheets("Export").Cells(ligne_active_ori, col_ht10).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = "E6"

    ligne_active_dest = ligne_active_dest + 1

  'ecriture ligne tva à 10%
    Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
    Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value
     Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_tva10
     Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)

     Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value

     Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = 0
    Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = Sheets("Export").Cells(ligne_active_ori, col_tva10).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = "E6"

    ligne_active_dest = ligne_active_dest + 1

End If

'***********

' test si montant 20% renseignés
    If Sheets("Export").Cells(ligne_active_ori, col_ht20).Value <> "" Then
    'Ecriture ligne TTC 20

Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value

Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_ttc20
Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)

Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value
Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value
Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = 0
Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = Sheets("Export").Cells(ligne_active_ori, col_ht20).Value + Sheets("Export").Cells(ligne_active_ori, col_tva20).Value
Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = ""

ligne_active_dest = ligne_active_dest + 1

'Ecriture lign HT 20%
  Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
    Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_ht20
    Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)

    Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = 0
    Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = Sheets("Export").Cells(ligne_active_ori, col_ht20).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = "E5"

    ligne_active_dest = ligne_active_dest + 1

  'ecriture ligne tva à 20%
    Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
    Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value
     Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_tva20
     Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)

     Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value

    Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = 0
    Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = Sheets("Export").Cells(ligne_active_ori, col_tva20).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = "E5"

    ligne_active_dest = ligne_active_dest + 1

End If
'----------------------------------------
' test si autoliquidation ( si ht = ttc)
    If Sheets("Export").Cells(ligne_active_ori, col_ht).Value = Sheets("Export").Cells(ligne_active_ori, col_ttc).Value Then
    'Ecriture ligne TTC

Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value

Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_ttc0
Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)

Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value
Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value
Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = 0
Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = Sheets("Export").Cells(ligne_active_ori, col_ttc).Value
Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = ""

ligne_active_dest = ligne_active_dest + 1

'Ecriture lign HT autoliq
  Sheets("Ecritures").Cells(ligne_active_dest, 1).Value = code_jnal
    Sheets("Ecritures").Cells(ligne_active_dest, 2).Value = Sheets("Export").Cells(ligne_active_ori, col_date).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 3).Value = cpte_ht0
    Sheets("Ecritures").Cells(ligne_active_dest, 4).Value = Right(Sheets("Export").Cells(ligne_active_ori, col_nfact).Value, 8)

    Sheets("Ecritures").Cells(ligne_active_dest, 5).Value = Sheets("Export").Cells(ligne_active_ori, col_lib).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 6).Value = Sheets("Export").Cells(ligne_active_ori, col_nfact).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 7).Value = 0
    Sheets("Ecritures").Cells(ligne_active_dest, 8).Value = Sheets("Export").Cells(ligne_active_ori, col_ht).Value
    Sheets("Ecritures").Cells(ligne_active_dest, 9).Value = "V9"

    ligne_active_dest = ligne_active_dest + 1

End If

'----------------------------------------------

End If

ligne_active_ori = ligne_active_ori + 1
Wend
Application.ScreenUpdating = True

MsgBox "Génération Terminée." & Chr(13) & Chr(13) & "Copier le contenu de la feuille Ecritures pour le coller dans ISACOMPTA (INTERFACE : IMPORT TABLEUR UNIVERSEL EXCEL...)."

End Sub

Merci d'avance à tous et force à vous :-)

Si vous voyez des erreurs ou incohérence dans le code n'hésitez pas à me le remonter

Merci

Hello,

Si j'ai bien compris, voici un exemple :

col_nfact = Worksheets("Paramètres").Range("b7").Value
col_date = Worksheets("Paramètres").Range("b8").Value
col_lib = Worksheets("Paramètres").Range("b9").Value
col_ht = Worksheets("Paramètres").Range("b10").Value

'....

code_jnal = Worksheets("Paramètres").Range("b21").Value
cpte_ht55 = Worksheets("Paramètres").Range("b22").Value

'....

Sheets("Ecritures").Cells(2, 1).Value = Worksheets("Paramètres").Range("b37").Value
Sheets("Ecritures").Cells(2, 2).Value = Worksheets("Paramètres").Range("b38").Value

Je vais le tester

Merci :-)

Merci, en voyant la solution, je me sent bête...

Merci beaucoup je crois que je m'embrouillé tout seul alors que la solution était toute simple.

Merci à toi :-)

Rechercher des sujets similaires à "automatisation vba feuille parametre"