XL 2016 Bon de commande avec édition automatique

[WoOdErOu]

Bonjour à tous,

J'essaye de créer une sorte de bon de commande automatique pour mon boulot qui aurait le fonctionnement suivant :

- Les commerciaux complètent chaque onglet "MARQUE x" avec le nombre de quantités pour chaque article

- Lorsqu'ils cliquent sur le bouton "éditer le bon de commande" sur l'onglet "Bon de Commande", une macro va chercher toutes les valeurs de la colonne "G" de chaque onglet "MARQUE" qui sont différents de "" ou 0, et les fait apparaître en colonne "J" de l'onglet "Bon de Commande".

Idéalement, il faudrait que ce soit la source qui soit inscrite dans cette colonne J, et pas la valeur brute, car j'ai fait une formule DECALE avec du INDIRECT et FORMULETEXT, qui permet de faire un décalé par rapport à la cellule source, et ainsi d'afficher automatiquement le marque, référence, désignation, prix, etc... de l'article commandé.

J'ai eu beau chercher depuis de nombreux jours tous types de solutions sur internet, mais mon niveau débutant sur VBA ne m'a pas permis d'avoir quelque chose d'efficace. J'imagine que le code doit être un espèce de LOOP qui dirait de descendre la colonne G des onglets "'MARQUE" ligne par ligne, et d'afficher la source de la cellule en colonne J de l'onglet "Bon de Commande", en faisant à chaque fois un OFFSET.

Mais là, on dépasse le niveau que j'ai actuellement !

Je vous remercie d'avance pour votre aide, et n'hésitez pas à me demander plus d'informations si nécessaire. Le fichier démonstration est en PJ.

Bon dimanche à vous tous.

Bien à vous,

WoOdErOu

 

[fanfan38]

Bonjour
Ci joint ma solution

A+ François

 

[Robert]

Bonjour Wooderou, bonjour le forum,

Avec un tableau cohérents dans l'onglet Bon de Commande, ça donne ça :

Le code :

Sub Macro1()
Dim BC As Worksheet 'déclare la variable BC (onglet Bon de Commande)
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim TL() As Variant 'déclare la variable TL (Tableau des Lignes)
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Integer 'déclare la variable J (incrément)
Dim K As Integer 'déclare la variable K (incrément)
Dim L As Integer 'déclare la variable L (incrément)

Set BC = Worksheets("Bon de Commande") 'définit l'onglet BC
BC.Range("A1").CurrentRegion.Offset(1, 0).ClearContents 'efface d'éventuelles anciennes données du Bon de Commande
K = 1 'initialise la variable K
For Each O In Worksheets 'boucle 1 : sur tous les onglets O du classeur
    If O.Name <> "Bon de Commande" Then 'condition 1 : si l'onglet ne se nomme pas "Bon de Commande"
        TV = O.Range("A1").CurrentRegion 'définit le tableau des valeurs TV
        For I = 3 To UBound(TV, 1) 'boucle 2 : sur toutes les lignes I du tableau des valeurs TV (en partant de la troisième)
            'condition 2 : si la donnée en colonne 7 du tableau des valeurs n'est ni nulle, ni vide
            If TV(I, 7) <> "" And TV(I, 7) <> 0 Then
                ReDim Preserve TL(1 To 7, 1 To K) 'redimensionne le tableau des lignes TL (7 lignes, K colonnes)
                For L = 1 To 7 'boucle 3 : sur toutes les colonnes du tableau des valeurs TV
                    TL(L, K) = TV(I, L) 'récupères dans la ligne L de TL la donnée en colonne L de TV (=> Transposition)
                Next L 'prochaine colonne de la boucle 3
                K = K + 1 'incrémente K (ajoute une colonne au tableau des lignes TL)
            End If 'fin de la condition 2
        Next I 'prochaine ligne de la boucle 2
    End If 'fin de la condition 1
Next O 'prochaine onglet de la boucle 1
If K > 1 Then 'condition : si K est supérieur à 1
    'renvoie dans la cellule A2 redimensionnée de l'onglet BC, le tableau TL transposé
    BC.Range("A2").Resize(UBound(TL, 2), UBound(TL, 1)).Value = Application.Transpose(TL)
    BC.Range("H2:H" & K).FormulaLocal = "=G2*E2" 'renvoie la formule du calcul du total dans la colonne H
    'efface les quantités des onglet MARQUE...
    For Each O In Worksheets 'boucle sur tous les onglet O du classeur
        'si l'onglet ne se nomme pas "Bon de Commande" efface les quantités marquées en colonne G
        If O.Name <> "Bon de Commande" Then O.Columns(7).SpecialCells(xlCellTypeConstants).ClearContents
    Next O 'prochain onglet de la boucle
End If 'fin de la condition
End Sub

[Édition]
Bonjour Fanfan, nos posts ses ont croisés...

Le fichier :

 

[WoOdErOu]

Bonjour,

Tout d'abord un grand merci à vous d'avoir répondu si vite. Fanfan38, ce n'est pas tout à fait cela que j'avais en tête mais je le garde de côté pour une future utilisation.

Robert, c'est exactement cela que je souhaitais. Mais lorsque j'essaye de l'appliquer à mon fichier original, cela bloque, au niveau de la ligne de code

For I = 3 To UBound(TV, 1) 'boucle 2 : sur toutes les lignes I du tableau des valeurs TV (en partant de la troisième)

et m'indique "Erreur d'exécution 13 ; Incompatibilité de type


EDIT2 : j'ai réussi à régler le problème. Je vais maintenant essayer de modifier un peu le fichier original pour qu'il ressemble véritablement à ce que je souhaite. Je vous tiens informé si je rencontre des difficultés!

Bien à vous,

WoOdErOu

 

[WoOdErOu]

Bonjour à tous,

Tout marche très bien, une fois adaptée à mon fichier Excel. J'y ai rajouté quelques petites touches par ci par-là et c'est exactement ce que j'avais en tête.

Encore un grand merci à toi Robert.

J'avais une dernière question : je souhaite que la dernière ligne soit un Total Général avec une somme dynamique qui irait de (première ligne ; dernière ligne) selon le nombre de références commandées.

J'ai pensé à plusieurs méthodes, et ai essayé de les mettre en pratique mais je suis bloqué :

- Mettre cette somme en cellule H3 ; et faire en sorte que le tableau qu'on transpose ne se "colle" pas, mais "s'insère". Ainsi, cela décalerait la somme en H3 d'autant de lignes qu'en contient le tableau transposé.

- Mettre cette somme en cellule H500 ; et faire une macro qui repère la dernière ligne du tableau transposé, la ligne 500, et mettent les lignes vides interposées en Hidden = True

Mais je n'arrive pas à retranscrire mais idées en code, et je continue d'avoir des messages d'erreur depuis 3 jours que j'essaye. Dois-je l'intégrer à ton code Robert ? ou en faire un nouveau, et ensuite faire un Call Macro1 ; Call Macro2 ?

Merci d'avance pour votre aide.

Bonne soirée,

WoOdErOu

 

[Robert]

Re,

Le code modifié :

Sub Macro1()
Dim BC As Worksheet 'déclare la variable BC (onglet Bon de Commande)
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim TL() As Variant 'déclare la variable TL (Tableau des Lignes)
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Integer 'déclare la variable J (incrément)
Dim K As Integer 'déclare la variable K (incrément)
Dim L As Integer 'déclare la variable L (incrément)

Set BC = Worksheets("Bon de Commande") 'définit l'onglet BC
BC.Range("A1").CurrentRegion.Offset(1, 0).ClearContents 'efface d'éventuelles anciennes données du Bon de Commande
K = 1 'initialise la variable K
For Each O In Worksheets 'boucle 1 : sur tous les onglets O du classeur
    If O.Name <> "Bon de Commande" Then 'condition 1 : si l'onglet ne se nomme pas "Bon de Commande"
        TV = O.Range("A1").CurrentRegion 'définit le tableau des valeurs TV
        For I = 3 To UBound(TV, 1) 'boucle 2 : sur toutes les lignes I du tableau des valeurs TV (en partant de la troisième)
            'condition 2 : si la donnée en colonne 7 du tableau des valeurs n'est ni nulle, ni vide
            If TV(I, 7) <> "" And TV(I, 7) <> 0 Then
                ReDim Preserve TL(1 To 7, 1 To K) 'redimensionne le tableau des lignes TL (7 lignes, K colonnes)
                For L = 1 To 7 'boucle 3 : sur toutes les colonnes du tableau des valeurs TV
                    TL(L, K) = TV(I, L) 'récupères dans la ligne L de TL la donnée en colonne L de TV (=> Transposition)
                Next L 'prochaine colonne de la boucle 3
                K = K + 1 'incrémente K (ajoute une colonne au tableau des lignes TL)
            End If 'fin de la condition 2
        Next I 'prochaine ligne de la boucle 2
    End If 'fin de la condition 1
Next O 'prochaine onglet de la boucle 1
If K > 1 Then 'condition : si K est supérieur à 1
    'renvoie dans la cellule A2 redimensionnée de l'onglet BC, le tableau TL transposé
    BC.Range("A2").Resize(UBound(TL, 2), UBound(TL, 1)).Value = Application.Transpose(TL)
    BC.Range("H2:H" & K).FormulaLocal = "=G2*E2" 'renvoie la formule du calcul du total dans la colonne H
    BC.Cells(K + 1, "H").FormulaLocal = "=Somme(" & "H2:H" & K & ")" 'place la formule de la somme
    'efface les quantités des onglets MARQUE...
    For Each O In Worksheets 'boucle sur tous les onglet O du classeur
        'si l'onglet ne se nomme pas "Bon de Commande" efface les quantités marquées en colonne G
        If O.Name <> "Bon de Commande" Then O.Columns(7).SpecialCells(xlCellTypeConstants).ClearContents
    Next O 'prochain onglet de la boucle
End If 'fin de la condition
End Sub