Génération de feuilles avec nomenclature

[Simon271982]

Bonjour,

Mensuellement je reçois le tarif d'un fournisseur mais il est pas facilement exploitable.
Il y a deux feuilles la nomenclature et le Tarif (environ 5000 lignes)
Je voulais savoir si il étais possible avec une macro de générer des feuilles par famille de produits (code 01 à 13)
Et dans chaque Famille de produit il y a des groupes de produit, serait il possible d'ajouter une ligne de couleur entre chaque Groupe avec l'intitulé du groupe (fourni dans la nomenclature).
Sachant que je reçois mensuellement le tarif que ce soit une macro que je puisse copié coller d'un fichier à l'autre.

Je vous remercie par avance.

Excellement

Simon

Nota (je me débrouille pour des formules simple, mais dès que c'est des macro je suis vite dépassé)

 

[Modeste]

Re : Génération de feuilles avec nomenclature

Bonjour Simon et bienvenue à toi,

Il va nous manquer quelques précisions:
- le contenu de la feuille nomenclature varie dans le temps? ou alors ce sont toujours les mêmes familles et mêmes groupes?
- ton tarif pour un mois donné est un nouveau tarif complet, ou il faudrait "fusionner" avec le précédent?
- on voit assez bien les groupes dans cette feuille nomenclature, mais le code alphanumérique qui figure devant l'intitulé correspond à quoi, dans la feuille Tarif? Celui qui y ressemble est le "code gen" ... mais il est sur 4 caractères!?
- peut-on "splitter" le contenu des colonnes Famille et Groupe de manière que le code soit dans une colonne distincte?
- pour les codes Famille, toujours 2 caractères et toujours 3 (ou 4?) pour les codes Groupe?
- dans ton tarif, les intitulés des colonnes ne varient jamais? L'ordre des colonnes non plus?

Voici déjà quelques éléments ... d'autres arriveront peut-être encore ...

 

[Simon271982]

Re : Génération de feuilles avec nomenclature

Bonjour,

- La nomenclature est toujours la même, pour le cas des familles
- on a toujours un tarif complet
- pour le code famille j'imagine pas de soucis et pour le code groupe, ça correspond au 3 premiers caractère du code Gen
- on peut splitter dans la feuille nomenclature il y a pas de soucis
- toujours 2 caractères dans la colonne Famille et toujours 4 dans la colonne Groupe
- les intitulés ne change jamais ( il font une extraction d'une base de donnée je pense)

pas de soucis si d'autre question.

 

[Modeste]

Re : Génération de feuilles avec nomenclature

pas de soucis si d'autre question

En voici encore une, alors
Si tu ne dois le faire qu'une fois par mois et que tu maîtrises les formules simples, tu accepteras bien de faire quelques manipulations préalables!?
Je t'explique:
En feuille nomenclature, te positionner dans la cellule vide à droite du premier groupe renseigné (dans ton exemple: E3) et y inscrire= =GAUCHE(D3;3), valider et double-cliquer sur la poignée de recopie
Dans la cellule voisine de la précédente, coller cette formule: =STXT(D3;5;99) (le 99 sera à adapter, si tu as des noms de groupes qui risquent de compter + de 99 caractères; dans ton exemple, le max est de 61). Double-cliquer, une fois de plus, sur la poignée de recopie.
En feuille Tarif, ajouter une colonne où tu voudrais voir apparaître les noms des groupes et, dans la première cellule de cette nouvelle colonne (ligne 11), saisir cette formule-ci: =RECHERCHEV(GAUCHE(E11;3);Nomenclature!$E$3:$F$99;2;FAUX). ici aussi, la plage Nomenclature!$E$3:$F$99 sera peut-être à adapter. Double-clique sur la poignée, une fois de plus, pour recopier la formule sur toute la hauteur (je suppose qu'il n'y aura pas de cellules vides dans ton tarif!?).
On peut, tant que toute la plage est sélectionnée (puisque tu viens de recopier!), la copier et, en restant au même emplacement, faire un collage spécial > Valeurs, de manière à remplacer les formules par leur résultat.

Tu m'as suivi jusque là? Ça te semble envisageable de faire ces manips, avant que la macro ne se charge de répartir dans les différentes feuilles?

 

[Simon271982]

Re : Génération de feuilles avec nomenclature

Je pense que c 'est jouable même si il va falloir que je me mettes les formules noir sur blanc pour m'en rappeler.

 

[JBOBO]

Re : Génération de feuilles avec nomenclature

Bonjour,

Ci joint un fichier à tester (sans macros)

 

[Modeste]

Re : Génération de feuilles avec nomenclature

Re,

En pièce jointe, j'ai fait les manipulations proposées dans mon précédent message: formules, puis copier-coller (spécial valeurs) dans la feuille Tarif. Les formules sont donc toujours en feuille nomenclature. J'ai ajouté les intitulés des groupes en colonne F (mais tu les mets où tu veux)

Pour tester la macro, une fois le fichier ouvert, Alt+F8, sélectionner la macro "répartir" et Exécuter
Pour voir le code, Alt+F11 et double-cliquer sur "Module1" à gauche
J'ai ajouté quelques commentaires au code

Si le résultat est concluant, supprime les feuilles 01 à 13, colle, en feuille "Tarif" les valeurs d'un tarif un peu plus complet et ré-exécute la macro (en n'oubliant pas d'ajouter une colonne pour les intitulés). Ne teste peut-être pas tout de suite avec 5000 lignes: il faudra voir si le temps d'exécution est "raisonnable" avec 500 lignes, puis 2000.
Mais il faut d'abord vérifier si j'ai bien compris, si le résultat est celui attendu, si je n'ai rien oublié, etc.

[Edit:] salut JBOBO!

 

[Simon271982]

Re : Génération de feuilles avec nomenclature

Bonjour JBOBO,

C'est vraiment nickel je te remercie ça correspond exactement à ma demande.
Juste une petite chose ou deux,
- Est il possible de recopier les premières lignes avec le logo et le reste car il y a nos conditions d'achat de spécifier.
- Est il possible lors de l’exécution de la macro de remettre sur chaque onglet les filtres de tri et le fait que les 10 première ligne soit figés.
- et au lieu d'un trait de bordure une ligne vide de couleur (et idéalement le nom du groupe)
- et de nommer les Onglets des familles.

Mais en tout cas ça marche bien sur la totalité du tarif

Simon

 

[Modeste]

Re : Génération de feuilles avec nomenclature

Bonjour Simon, JBOBO,

À lire le début du message, j'ai cru que l'ensemble s'adressait à JBOBO !?
Comme tu reparles de macro, j'ai un doute ...

Ceci dit:

nommer les Onglets des familles

n'est possible que si les intitulés des familles se limitaient à 31 caractères, ce qui n'est pas le cas de la 10!

---

remettre sur chaque onglet les filtres de tri

Euh ... tu parles d'activer le filtre automatique??

au lieu d'un trait de bordure une ligne vide de couleur

Si je comprends bien cette demande, elle me paraît totalement incompatible avec ce que j'ai compris de la précédente

---

recopier les premières lignes avec le logo et le reste car il y a nos conditions d'achat de spécifier

Le plus simple serait sans doute d'avoir, dans le classeur, une feuille "modèle" qu'on copierait, plutôt que de créer des feuilles vierges. Il faudrait également préciser alors, à partir de quelle ligne les données doivent être insérées! Si des lignes sont figées dans le modèle, elle le seront dans les copies.

 

[Simon271982]

Re : Génération de feuilles avec nomenclature

Excuse moi Modeste, c'était bien à toi que je m'adressais j'avais pas vu le post de JBOBO j'ai cru que c'était toi qui avais signer JBOBO

Pour les Onglets même si c'est tronquer le nom c'est pas spécialement grave
oui mettre un filtre automatique car sur le filtre automatique il y a la possibilité de recherche et quand on utilise le fichier de façon informatique c est très commode, le système du trait de bordure c'est pour les personne qui l'utilise au format papier.

La ligne vide plutot que le trait de bordure car lorsqu'on imprime le document ça ressort pas beaucoup en noir et blanc
alors qu'un ligne de cellule de couleur ressort beaucoup mieux.

Pour ce qui est des lignes de départ c est exactement le positionnement de ce que j'ai fait passer le tarif commence toujours à la ligne 11

 

[Modeste]

Re : Génération de feuilles avec nomenclature

Re,

Maintenant que tu sais que JBOBO n'est pas un second volet de ma personnalité et que l'homme (qui existe bel et bien) a consacré du temps à ta question, il me semble qu'il aurait bien droit à un petit merci aussi (ça ne coûte rien!)

Je ne suis (mais tu l'avais sans doute compris) pas chaud pour ton idée de ligne vide colorée: si on la met en œuvre, tu peux faire une croix sur tes filtres automatiques!
J'ai donc mis une bordure épaisse et noire, à la place de la précédente.

J'ai ajouté une feuille "Modèle" comme évoqué précédemment.
Assure-toi que ce sera bien en ligne 10 que les titres devront apparaître, dans chaque feuille (et les données à partir de la ligne 11)

 

[Simon271982]

Re : Génération de feuilles avec nomenclature

Bonjour Modeste... et merci pour tout.

C'est vraiment nickel.
Pour l'histoire de la ligne vide si tu me dis qu'il faut pas la mettre je te fais confiance car "you are the boss".

Ce que j'aurais a faire c'est garder les feuilles Nomenclature et Modèle et de coller la feuille Tarif dans ce classeur. comme ça j'aurais pas à faire la manip des formules et de copier - coller la macro

Et je te remercie encore une fois.

Excellement

Simon

 

[Simon271982]

Re : Génération de feuilles avec nomenclature

Bonjour Modeste,
depuis presque 1 ans la macro marche de façon parfaite je n'ai aucun problème jusqu'à hier soir.
J'ai voulu ajouter deux colonnes une avec un pourcentage de remise (colonne L) et une avec un montant HT remisé (colonne M) (calculé en fonction du pourcentage) dans le modèle et dans la feuille tarif, ça a pas marché comme je voulais donc j'ai supprimé et j'ai voulu relancer la macro, depuis ça ne marche pas correctement est ce que tu peux me venir en aide. Et si tu peux m'aider à gérer ces deux colonnes sachant que dans le tarif brut c'est moi qui rajoute un pourcentage de remise à toute les lignes. Par exemple j'applique un pourcentage de 5% sur tout le tarif ça calcul automatiquement le montant remisé. Je lance la macro ça me scinde par famille et après je peux appliquer un pourcentage différent par famille.
Est ce que tu me comprends ?

Simon

 

[Modeste]

Re : Génération de feuilles avec nomenclature

Bonjour Simon, le reste du forum,

Est ce que tu me comprends ?

Par prudence, au bout d'un an, je vais dire: "oui, dans les grandes lignes"

La première chose à vérifier -puisque le code utilise le principe du Filtre Avancé- est de s'assurer que tu as bien donné des titres à tes deux colonnes supplémentaires et recopié (à l'identique) ces deux titres dans la feuille "modèle".
Pour le reste (si j'ai bien compris) il n'y aurait que 2-3 petites adaptations à apporter au code:

Sub répartir()
Application.ScreenUpdating = False
With Sheets("Tarif")
On Error Resume Next
.ShowAllData
Set tarif = .Cells(10, 1).Resize(Application.CountA(.[A10:A50000]), 13)
.[M1] = "FAMILLE"
For fam = 1 To 13
    Worksheets("Modèle").Copy after:=Sheets(Sheets.Count) ' ajouter 1 feuille en dernière position
    ActiveSheet.Name = Mid(Sheets("Nomenclature").Cells(2 + fam, 1), 4, 31) 'la nommer
    .[M2] = fam 'num de famille dans zone de critère
    tarif.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.[M1:M2] 'filtre avancé "sur place"
    tarif.SpecialCells(xlCellTypeVisible).Copy [A10] 'copie des lignes filtrées dans feuille active
    Range("A:M").EntireColumn.AutoFit 'ajustement auto de la largeur des colonnes
    [A10].CurrentRegion.Sort key1:=[E11], Header:=xlYes 'tri sur n° de groupe (inutile si tri déjà réalisé sur n° de groupe dans Tarif)
    For i = 12 To [E50000].End(xlUp).Row 'colonne CODE GEN dans feuille créée
        If Cells(i, 5) <> Cells(i - 1, 5) Then
            With Cells(i, 1).Resize(1, 13).Borders(xlEdgeTop)
                .ColorIndex = xlColorIndexAutomatic 'bordure noire au-dessus
                .Weight = xlThick 'trait gras
            End With
        End If
    Next i
    .ShowAllData
Next fam
.[M1:M2].Clear
End With
Application.ScreenUpdating = True
End Sub

 

[Simon271982]

Re : Génération de feuilles avec nomenclature

Bonjour Modeste et tout d abord merci

J'ai modifié la macro, par contre je comprend pas j'ai trois onglets qui ne se renomme pas en fonction de de la nomenclature Famille :
03 PRODUITS POUR BETON ARME
08 FONTES-ACCESSOIRES
09 METAUX NON FERREUX - TUBE CUIVRE

Je comprend pas pkoi ces trois là ne se renomme pas correctement