XL 2016 Boucle pour ajouter des colonnes à un onglet

Pauliakov

XLDnaute Nouveau
Bonjour à tous,

Je suis encore novice en VBA mais je commence à vraiment m'y mettre car c'est très utile et très prenant !

Je suis actuellement en train de créer un fichier pour mon travail qui servira à la Supply Chain.
Mon fichier est alimenté par un fichier SQL mis à jour dans l'onglet SQL
Je souhaite réaliser une "Fiche_Fournisseur" qui correspond à un onglet
Donc un onglet = 1 fournisseur

La trame de chaque onglet est identique et l'originale se nomme "Modèle"
Dans le fichier SQL, le nom du fournisseur est construit comme ça
Exemple : Fournisseur "DUPONT" dans mon SQL c'est 9DUPONT0000000"

Mais je bloque complètement (manquant d'éléments) afin d'automatiser la macro de la création des onglets.

Pour résumé, je souhaite que pour chaque ligne contenant dans la colonne 3 le nom d'un fournisseur, la macro crée un onglet au nom du fournisseur, ajouter son nom en G2 (juste le nom est non le numéro avec le 9 et les 0 si possible) et ajoute les colonnes en rouge de l'onglet SQL dans chaque fiche. J'ai indiqué dans quelle colonne doit aller la colonne en dessous.
De plus, il faut un filtre au début de la macro sur la colonne D avec les caractères BCF car sinon j'aurais des lignes non voulu.

Evidemment les colonnes ne changent pas, mais les lignes varient.
De plus, j'ai 5 onglets qui ne doivent pas bouger. J'ai essayé de mettre If avec leur nom pour pas que ça boucle sur eux, mais ça ne fonctionne pas...

J'ai commencé comme ça

VB:
Dim Fiche_Fournisseur As Worksheet

Worksheets ("SQL").Range("A1").AutoFilter field:=4, Criteria1:=BCF

For Each Fiche_Fournisseur In Worksheets

'On met des exceptions sur les feuilles qui doivent rester intactes
If Fiche_Fournisseur = "SQL" Or Fiche_Fournisseur = "Infos" Or Fiche_Fournisseur = "Assistant" Or Fiche_Fournisseur = "Suivi_AR" Or Fiche_Fournisseur = "Modèle" Then

Else If
'Je supprime toutes les données des fiches pour faire place aux nouvelles
Sheets("Fiche_Fournisseur").Range("C4:S254").Clear
Next

Et donc le but étant de mettre ces fiches à jour si j'ai des nouveaux fournisseurs dans le SQL, d'une manière automatique car la macro crée une nouvelle fiche à chaque nouveau fournisseur et mets à jour les fiches facilement.

Je pense que ce n'est pas facile... J'ai déjà fait la macro pour délimiter la zone de travail :

VB:
Dim zone As Range
Dim DernLigne As Long, DernColonne As Integer
'dernière ligne colonne A
DernLigne = Range("A" & Rows.Count).End(xlUp).Row
'dernière colonne ligne 1
DernColonne = Cells(1, Cells.Columns.Count).End(xlToLeft).Column - 12
Set maPlage = Range(Cells(1, 1), Cells(DernLigne, DernColonne))
zone.Select

Mais pour la suite, je n'y arrive pas, j'ai fait des forums, j'ai lu des FAQ ou autres , mais je n'arrive pas à maîtriser tous les éléments.
Dans un soucis de confidentialité, je suis obligé de masquer toutes les données. L'onglet SQL est donc un copier coller vierge afin de voir la mise en page. J'ai remplacé le nom des fournisseurs par les lettres A, B, C etc.

Si vous avez des pistes j'en serais ravie !

Par avance, merci de votre aide !
 

Pièces jointes

  • Test.xlsm
    170.3 KB · Affichages: 36

vgendron

XLDnaute Barbatruc
Bonjour et bienvenu

Ton besoin est assez clair, mais il manque quelques précisions.

Tu dis que à partir de la feuille SQL, tu souhaites créer un onglet par fournisseur selon l'onglet "Modèleé
hors.. dans ton premier code, tu sembles effacer les dites feuilles.. cela veut il dire que l'onglet du fournisseur existe parfois déjà?
--> dans ce cas..il faut le mettre à jour

Pour effacer ces onglets existants, tu fais une boucle sur tous les onglets SAUF 4
cette partie de code ne fonctionne effectivement pas
VB:
For Each Fiche_Fournisseur In Worksheets

'On met des exceptions sur les feuilles qui doivent rester intactes
If Fiche_Fournisseur = "SQL" Or .....

Fiche_Fournisseur c'est l'OBJET feuille
toi. il te faut un test sur son nom
donc
VB:
For Each Fiche_Fournisseur In Worksheets

'On met des exceptions sur les feuilles qui doivent rester intactes
If Fiche_Fournisseur.name= "SQL" Or .....

sinon, je ne vois pas où se trouve ton code?.. quel formulaire?
tu parles d'un filtre sur la colonne D.. de la feuille SQL ?
tu dis aussi avoir remplacé les noms de fournisseurs par A B C .. je ne vois pas ou..
 

Pauliakov

XLDnaute Nouveau
Bonjour,

Merci pour votre réponse, voici les points d’éclaircissement.

Tu dis que à partir de la feuille SQL, tu souhaites créer un onglet par fournisseur selon l'onglet "Modèleé
hors.. dans ton premier code, tu sembles effacer les dites feuilles.. cela veut il dire que l'onglet du fournisseur existe parfois déjà?
--> dans ce cas..il faut le mettre à jour

Oui alors en fait je parle de maintenant. Je m'explique :
A l'heure actuelle je n'ai pas d'onglet fournisseur car je n'ai pas utilisé la macro. Quand je vais l'utiliser, je vais peut être avoir 9 fournisseurs, donc 9 onglets. Imaginons qu'à la prochaine mise à jour, j'ai un autre fournisseur qui apparait dans le SQL, alors je crée un nouvel onglet pour ce fournisseur, et mets les données à jour pour les autres fiche fournisseur.

Fiche_Fournisseur c'est l'OBJET feuille
toi. il te faut un test sur son nom
donc

Ok je ne savais pas comment faire ça, pour If c'est ok merci

sinon, je ne vois pas où se trouve ton code?.. quel formulaire?
Je viens de voir que j'ai oublié de l'intégrer haha, il devrait être dans module2. Je l'ai ajouté

tu parles d'un filtre sur la colonne D.. de la feuille SQL ?
Oui alors le filtre est dans SQL colonne D car j'ai deux types de document dans le SQL. Des BL et des Commandes. Pour cette macro, seulement les commandes (nommé BCF dans mon SQL) m'intéressent, d'où la nécessite de faire un filtre avant la macro, à moins qu'il y est une autre solution.. ^^

tu dis aussi avoir remplacé les noms de fournisseurs par A B C .. je ne vois pas ou..
C'est une erreur de ma part. J'ai utilisé l'exemple DUPONT à la place. En fait c'était pour dire comment était écrit mon SQL. C'est juste que je ne peux pas écrire le nom des fournisseurs dans les onglets, SQL etc
 

Pièces jointes

  • Test.xlsm
    162.9 KB · Affichages: 22

vgendron

XLDnaute Barbatruc
Déjà.. un code spécial pour effacer les feuilles fournisseurs
VB:
Sub EffaceFournisseurs()
For Each Fiche_Fournisseur In Worksheets
    'On met des exceptions sur les feuilles qui doivent rester intactes
    If Fiche_Fournisseur.Name <> "SQL" And Fiche_Fournisseur.Name <> "Infos" And Fiche_Fournisseur.Name <> "Assistant" And Fiche_Fournisseur.Name <> "Suivi_AR" And Fiche_Fournisseur.Name <> "Modèle" And Fiche_Fournisseur.Name <> "Suivi_BL" And Fiche_Fournisseur.Name <> "!" Then
        'Je supprime toutes les données des fiches pour faire place aux nouvelles
        Sheets(Fiche_Fournisseur.Name).Range("C4:S254").ClearContents
    End If
Next Fiche_Fournisseur

End Sub

j'ai ajouté dans les exceptions la feuille "!"
 

vgendron

XLDnaute Barbatruc
et un début de réponse pour le reste pour déjà te faire une idée
VB:
Sub Maj()

'On définit le type des variables
Dim Fiche_Fournisseur As Worksheet
Dim maPlage As Range
Dim DernLigne As Long, DernColonne As Integer

EffaceFournisseurs 'on appelle la macro pour effacer les feuilles existantes

With Sheets("SQL")
    'dernière ligne colonne A
    DernLigne = .Range("A" & .Rows.Count).End(xlUp).Row
    'dernière colonne ligne 1
    DernColonne = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column - 12 'pourquoi moins 12 ?

    'Je filtre les commandes et non les BL
    .Range("A1").AutoFilter field:=4, Criteria1:="BCF"

    Set maPlage = .Range("A2").Resize(DernLigne, DernColonne).SpecialCells(xlVisible)
   
    maPlage.Select
    'MsgBox maPlage.Rows.Count
End With
'boucle sur les fournisseurs colonne C
For Each Fournisseur In maPlage.Columns(3)
    MsgBox Fournisseur
    Fournisseur = WorksheetFunction.Substitute(Fournisseur, "9", "") 'on supprime le 9 en début
    Fournisseur = WorksheetFunction.Substitute(Fournisseur, "0", "") 'on supprime les 0 de fin
    DéjàCréé = False
    For Each ws In Sheets 'on cherche si le fournisseur existe déjà
        If ws.Name = Fournisseur Then
            DéjàCréé = True
            Exit For
        End If
    Next ws
    If DéjàCréé = False Then 'la feuille n'existe pas, on la crée donc en copiant la feuille Modèle
        Sheets("Modèle").Copy after:=Sheets("Modèle")
        Sheets("Modèle (2)").Name = Fournisseur 'et on lui donne le nom
    End If
    'on peut maintenant mettre à jour les data
   
Next Fournisseur



End Sub
 

Eric C

XLDnaute Barbatruc
Bonjour le forum
Bonjour Pauliakov, bonjour vgendron

Des réponses comme je les aime avec des explications de lignes de code. Il est certain que cela
prend du temps au (à la) forumeur(se) mais c'est beaucoup plus clair. MERKI.
@ +Eric C
 

vgendron

XLDnaute Barbatruc
Hello Eric
les commentaires.. je les mets autant pour vous que pour moi.. :)
imagine.. revenir sur ce fichier dans 6 mois sans aucun commentaire...

sur ce.. bon appétit :-D
VB:
Sub Maj()

'On définit le type des variables
Dim Fiche_Fournisseur As Worksheet
Dim maPlage As Range
Dim DernLigne As Long, DernColonne As Integer

EffaceFournisseurs 'on appelle la macro pour effacer les feuilles existantes

With Sheets("SQL")
    .Activate
    'dernière ligne colonne A
    DernLigne = .Range("A" & .Rows.Count).End(xlUp).Row
    'dernière colonne ligne 1
    DernColonne = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column '-12 -->pourquoi moins 12 ?

    'Je filtre les commandes et non les BL
    .Range("A1").AutoFilter field:=4, Criteria1:="BCF"

    Set maPlage = .Range("A2").Resize(DernLigne, DernColonne).SpecialCells(xlVisible)
   
    maPlage.Select
    'MsgBox maPlage.Rows.Count
End With
'boucle sur les fournisseurs colonne C
For Each Fournisseur In maPlage.Columns(3)
    'MsgBox Fournisseur
    'on doit créer une nouvelle plage qui contient toutes les lignes du meme fournisseur
    '-->trouver un moyen de détecter s'il a déjà été traité.
   
    Fournisseur = WorksheetFunction.Substitute(Fournisseur, "9", "") 'on supprime le 9 en début
    Fournisseur = WorksheetFunction.Substitute(Fournisseur, "0", "") 'on supprime les 0 de fin
    DéjàCréé = False
    For Each ws In Sheets 'on cherche si le fournisseur existe déjà
        If ws.Name = Fournisseur Then
            DéjàCréé = True
            Exit For
        End If
    Next ws
    If DéjàCréé = False Then 'la feuille n'existe pas, on la crée donc en copiant la feuille Modèle
        Sheets("Modèle").Copy after:=Sheets("Modèle")
        Sheets("Modèle (2)").Name = Fournisseur 'et on lui donne le nom
    End If
    'on peut maintenant mettre à jour les data
   
        'For j = 1 To maNewPlage.Rows.Count 'au cas où le fournisseur existe plusieurs fois
        With Sheets(Fournisseur)
            .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 4)
            .Range("E" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 7)
            .Range("F" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 8)
            .Range("G" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 9)
            .Range("I" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 10)
            .Range("H" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 11)
            .Range("J" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 13)
            .Range("K" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 15)
            .Range("L" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 17)
            .Range("D" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 18)
        End With
        'Next j
Next Fournisseur
End Sub
 

Pauliakov

XLDnaute Nouveau
Bonjour Eric, re-bonjour vgendron,

Des réponses comme je les aime avec des explications de lignes de code. Il est certain que cela
prend du temps au (à la) forumeur(se) mais c'est beaucoup plus clair. MERKI.

Pas de soucis je vais mettre des commentaires à chaque fois !

Je vous remercie pour vos réponses !

Pour répondre aux premières questions oui en effet le fournisseur peut être dans le SQL plusieurs fois (en fait, plusieurs centaines de fois ahah) vu que chaque ligne correspond à une ligne de commande. Et donc je peux avoir plusieurs lignes de commande dans une commande. Enfin voilà, j'ai plusieurs fois le même fournisseur dans la colonne ^^

J'ai regardé le code, je comprends les éléments. Je vous fais un retour ce soir car je suis au travail.

J'ai juste une question pour la dernière ligne de cette série :
'For j = 1 To maNewPlage.Rows.Count 'au cas où le fournisseur existe plusieurs fois

With Sheets(Fournisseur)
.Range("C" & .Rows.Count).End(xlUp).Offset(1, 0) = maPlage.Item(1, 4)

maplage.Item(1,4) je ne comprends pas l'élément. Offset j'ai compris que l'on prend pas le titre de la colonne mais pas Item.

Merci par avance, j'essaye tout ce soir et si j'éprouve des difficultés je reviens vers vous :)
 

vgendron

XLDnaute Barbatruc
Re moi
donc voici la macro qui permet d'exporter toutes les infos pour les différents fournisseurs
--> necessite une feuille en plus.. "ResultFiltre"
pour chaque fournisseur, s'il apparait plusieurs fois.. toutes les lignes seront copiées en une seule fois..
la macro "MAJ" à lancer est dans le module 2
 

Pièces jointes

  • Pauliakov.xlsm
    216.1 KB · Affichages: 25

Pauliakov

XLDnaute Nouveau
Bonsoir à vous,

Avant tout, mille merci pour cette aide ! :D
J'ai bien regardé la macro, votre fichier "Pauliakov" qui fonctionne correctement, par contre quand je prends la macro et que je l'importe dans mon fichier, elle se lance, tourne mais à la fin, je n'ai ni onglet, ni données. Mais j'ai compris pourquoi et adapté la macro. Explications :

.Range("A1").AutoFilter Field:=4, Criteria1:="BCF" 'on filtre la colonne D avec BCF
.Range("A1").AutoFilter Field:=4, Criteria1:="BCF " 'on filtre la colonne D avec BCF avec des espaces dernières pour mettre les chiffres
.Range("A1").AutoFilter Field:=4, Criteria1:="BCFXXXXXX" 'on filtre la colonne D avec BCF avec des X pour simuler les autres caractères
.Range("A1").AutoFilter Field:=4, Criteria1:=Array("BCF" _
, "BCF160000", "BCF161200"), Operator:=xlFilterValues 'mais là je dois tout mettre alors impossible

.Range("A1").AutoFilter Field:=4, Criteria1:=Array("=*BCF*") puis j'ai trouvé ça et ça fonctionne nickel !
Pour vous expliquez, je vous ai mal indiqué un détail. Je n'ai pas juste BCF. En fait BCF est suivi d'un nombre comme ceci BCF164850 etc etc. J'ai donc inclus dans le filtre tout ce qui commence par BCF


Petite réponse à votre question dans la macro :
'dernière colonne ligne 1
DernColonne = .Cells(1, .Cells.Columns.Count).End(xlToLeft).Column '-12 -->pourquoi moins 12 ?
joute que si n'existe pas déjà
Next fournisseur
Pourquoi -12 ? C'était pour une autre manipulation, et je voulais pas les dernières colonnes, j'ai trouvé que cette solution :rolleyes:


Ensuite j'ai vu le petit truc à faire moi même pour mettre la désignation, merci du tuto ;) au moins j'ai compris la trame et je peux l'adapter au besoin. Et c'est plus intelligent car on ne fait pas un bête copier/coller.

Alors merci beaucoup ! J'aurais juste deux petites choses afin d'être refait complet.

1. Y a t-il moyen que la colonne "Date de livraison souhaitée" de chaque onglet se trie du plus récent au plus ancien dans la macro ?
2. Que les onglets se masquent à la fin de la macro pour avoir moins d'onglets car en fait mon but et d'appeler la fiche du fournisseur dans l'userform.
3. Ajouter le nom du fournisseur de la fiche en F2 de chaque fiche. Je n'arrive pas à utiliser la variable fournisseur afin de l'intégrer dans la cellule

Dans Assistant -> ouvrir une fiche fournisseur -> choisir le nom (donc la liste des onglets) ouvrir pour l'ouvrir et fermer pour la refermer.
Je n'ai aucuns soucis pour faire l'userform, seul la liste me bloque. Je souhaite importer le nom de tous les onglets dans l'onglet "Infos" de A2 à AXX en fonction du nombre de fournisseur, mais je n'y arrive pas. Le but étant ensuite d'intégrer la liste en RowSource dans la ListBox du UserForm.

Sinon je pense faire la méthode rudimentaire en prenant tous les fournisseurs manuellement dans mon système informatique, les mettre tous dans l'onglet "Infos" en RowSource dans la ListBox et si il n'y a pas de fiche ça me sort un message d'erreur "pas de commande en cours chez ce fournisseur" mais si on intègre des nouveaux fournisseurs, ça ne fonctionnera pas, c'est pourquoi je préfère l'intégrer par la macro en direct et à chaque nouveau fournisseur, il s'ajoute à la liste, sans se supprimer si il n'a pas de BCF en cours.




J'y pense.. il faudrait ajouter en début de macro un application.screenupdating=false
J'y pensais j'allais l'intégrer à la fin quand tout sera fait ;)

Merci et bonne soirée à vous tous !

Cordialement
 
Dernière édition:

vgendron

XLDnaute Barbatruc
Hello
Voici une version 2
1) le tri sur la date de livraison (colonne K) est intégré
2) on masque la feuille en fin de traitement
3)le nom du fournisseur reprend directement le nom de la feuille: fonction "=nomfeuille()" -->à vérifier que cette fonction existe dans ta version d'excel...
4) pour la liste des fournisseurs:
--> j'utilise une zone nommée dynamiquement (ouvrir gestionnaire de noms: ListeFournisseurs)
cette zone nommée est donnée directement en RowSource du ComboBox
une macro MajListeFournisseurs permet de lister tous les fournisseurs dans ta colonne A feuille Info..
la question c'est: est ce qu'on remet la liste à zéro à chaque fois (le plus simple niveau code..)
ou fait on un ajout des fournisseurs nouveaux et donc un tri alphabétique ---> et donc comment ajoutes tu un nouveau fournisseur?
 

Pièces jointes

  • Pauliakov2.xlsm
    217.7 KB · Affichages: 28

Pauliakov

XLDnaute Nouveau
Bonjour à tous,

Alors pour le début merci beaucoup.

Ensuite, je pense qu'on ne s'est pas compris, j'ai dans la fiche modèle en F2 "NOM FOURNISSEUR" et je souhaite intégrer le nom de la fiche aussi dans cette cellule, mais ce n'est qu'optionnel. Je pense que je peux le faire avec la variable fournisseur que vous créez dans la macro, mais je n'arrive pas à construire une ligne VBA pour l'ajouter.

Pour les fournisseurs dans la feuille Infos, j'aimerai seulement ajouter les nouveaux fournisseurs. Si c'est trop compliqué, on peut partir sur une remise à zéro.

Et donc quand j'en ajoute un, c'est dans mon système, donc mon SQL aura dans la colonne des autres fournisseurs ce nouveau fournisseur, la macro le détectera donc quand elle créera la nouvelle fiche fournisseur au nom de ce dernier

Je vous remercie pour votre aide
 

vgendron

XLDnaute Barbatruc
Pour la liste des fournisseurs à mettre à jour
VB:
Sub MajListeFournisseurs()
'liste tous les onglets fournisseurs dans la colonne A de la feuille "Infos"

'on efface toute la colonne A de la feuille Infos?
'ou alors, on ajoute uniquement s'il est absent??

For Each Fiche_Fournisseur In Worksheets
    'On met des exceptions sur les feuilles qui doivent rester intactes
    If Fiche_Fournisseur.Name <> "SQL" And Fiche_Fournisseur.Name <> "Infos" And Fiche_Fournisseur.Name <> "Assistant" And Fiche_Fournisseur.Name <> "Suivi_AR" And Fiche_Fournisseur.Name <> "Modèle" And Fiche_Fournisseur.Name <> "Suivi_BL" And Fiche_Fournisseur.Name <> "!" And Fiche_Fournisseur.Name <> "ResultFiltre" Then
        With Sheets("Infos")
            Set trouvé = .Range("A3:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Find(Fiche_Fournisseur.Name)
            If trouvé Is Nothing Then 'si nouveau fournisseur: on l'ajoute en bas
                .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0) = Fiche_Fournisseur.Name
                'tri??
                fin = .Range("A" & .Rows.Count).End(xlUp).Row
                .Sort.SortFields.Clear  'on supprime les tris actifs
                .Sort.SortFields.Add Key:=Range("A3:A" & fin), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                With .Sort
                    .SetRange Range("A2:N" & fin)
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
            End If
        End With
    End If
Next Fiche_Fournisseur
End Sub

Pour le nom du fournisseur en F2
la formule =nomfeuille() te donne directement le résultat attendu , vu que l'onglet porte le nom du fournisseur.. donc pas besoin de ligne de code
maintenant, si tu tiens absolument au code vba, suffit d'intégrer cette ligne au bon endroit dans la partie
VB:
With Sheets(fournisseur)
   ....
  .Range("F2")=ournisseur
end with
 

Discussions similaires

Réponses
6
Affichages
256

Statistiques des forums

Discussions
311 720
Messages
2 081 915
Membres
101 838
dernier inscrit
Christelle.B86