Macro - Remplissage et Impression Auto

Hello,

Aujourd’hui au menu une ‘simple’ gestion de stock

La feuille ‘Stock_In’ reprend sous forme de tableau les palettes envoyées en stock (chambre froide)

Date, numéro du BL, code produit, description, et quantité de palettes envoyées pour ce même code

La feuille ‘BL’ représente le bon de livraison que j’imprime et je mets sur la palette avant de l’envoyer. J’imprime chaque BL quatre fois, un pour chaque côté de la palette filmée

Je souhaiterais SVP une macro qui me permette de :

- imprimer automatiquement (en remplissant les cellules J3, A6 et A14) 4 exemplaires de chaque ligne du tableau pour un numéro de BL donné

Exemple : pour le AB20-01 je souhaiterais qu’il y ait 12 feuilles qui sortent de l’imprimante (car j’envoie 2 palettes de ABCD025 et 1 palette de ABCD020)

4 pour ABCD025

4 pour ABCD025

4 pour ABCD020

- enregistrer en un fichier PDF les produits envoyés dans mon exemple

3 feuilles > ABCD025, ABCD025, ABCD020

La cellule J3 pourrait être remplie comme ce qui suit

J3 : le numéro de palette, typiquement ça doit commencer a 1

Exemple : pour ABCD025 pour les 4 premières feuilles imprimées, j’aurais en J3 la valeur 1

pour ABCD025 la deuxième série des 4 feuilles imprimées, j’aurais en J3 la valeur 2

pour ABCD020 les dernières 4 feuilles imprimées, j’aurais en J3 la valeur 3

Si vous avez d’autres idées pour la structure je prends

Merci a tous pour votre temps et votre savoir

Hello,

J'ai refléchi encore et encore et avant qu'il ne soit trop tard, peut-être me permettrez vous cette légère modification qui viendrait simplifier le schéma

Dans le feuille Stock_In, dans le tableau colonne E "Quantité" au lieu d'inscrire 2, peut-être serait-il plus simple que par défaut la quantité soit 1 et si j'envoie deux palettes (même code et même description) je crée deux lignes (ça m'arrange pour les coûts, car si j'envoi deux palettes, je peux en sortir une demain et la deuxieme plusieurs jours après)

Merciii

Bonjour

Pour la rubrique Code, vous voulez quoi dans cette rubrique car dans votre feuille stock, vous avez un doublon. D'où si vous utilisez la cellule J2, cela ne va vous renvoyer que la première ligne donc ABCD025...

Merci Dan,

Je pense qu'avec un fichier ça sera plus clair, d'autant plus que j'ai adapté la feuille Stock_In à ma légère modification

J'ai ajouté 3 feuilles qui correspondent à mes attentes, je pense que tu comprendras tout de suite, c'est assez explicite

sorry, j'ai envoyé trop vite le message précédent

Ok. Je vois dans le dernier fichier

Ce que l'on peut faire, c'est juste un code qui va générer les feuilles sur base de la feuille BL considérée comme modèle et que l'on dupliquera pour chaque ligne de la feuille Stockin

Bon cela ?

Bonjour Dan,

Oui ça peut le faire

Pourrait-on mettre trois boutons dans la feuille Stock_In

1. "Générer les BL" > cette macro va créer les feuilles supplémentaires comme tu l'as si bien proposé

2. "Imprimer les BL" > cette macro va imprimer les feuilles générées en 4 exemplaires chacune

3. "Enregistrer en PDF" > va enregistrer sur un dossier les feuilles générées afin de garder un historique

Cela te paraît possible ?

Bonjour dani,

Le fichier en retour pour test :

- La feuille BL est considérée comme feuille Modèle. Je l'ai remaniée pour supprimer un maximum de cellules fusionnées (toujours à éviter quand c'est possible avec VBA.

- Bouton Bon de livraison : crée une feuille en dupliquant la feuille BL et ce, pour chaque ligne de la feuille Stockin. A noter que le bouton supprime toujours toutes les feuilles BL créées au préalable avant de générer de nouvelles feuilles
- Bouton Imprime : Imprime chaque feuille BL en 4 exemplaires
- Bouton Sauvegarde Pdf : Sauve chaque feuille BL créée dans le répertoire défini par la variable Chemin. Par défaut je l'ai supposé dans le même répertoire que le fichier Stock.

Cordialement

Je te remercie sincèrement pour ton temps, voici mes retours

BON LIVRAISON

D’une manière générale, j'ai compris que cette macro dupliquait la feuille BL avec les informations provenant du tableau de la feuille Stock_In, donc pour toutes les mises en formes, en-tête etc.. que je souhaiterais apporter, je modifie simplement le modèle, c’est bien ça ?

Deux retours sur cette macro

1. Serait-il possible que la macro ne s’exectue à chaque fois que pour les nouvelles lignes, car je serai amené à alimenter le tableau et si à chaque lancement de la macro, toutes les lignes sont prises en compte ce sera pas très lisible et je vais plus me retrouver

2. Serait-il possible d’avoir en J3 une info type « Numéro de palette / Nombre total de palettes » dans notre exemple ça donnerait

BL1 en J3 > « 1/3 »

BL2 en J3 > « 2/3 »

BL3 en J3 > « 3/3 »

IMPRIMER BL

RAS, c’est top, chaque BL est imprimé en 4 exemplaires.

SAUVE PDF

Je n’ai pas pu tester cette macro car il y a un problème apparemment (voir images)

Si je veux modifier le chemin pour l’enregistrement, est-ce que ma modification est bonne, ponctuation incluse

Chemin = ThisWorkbook.Path & "\"

Par

Chemin = "C:\Users\monnom\mondossier\"

Merci encore

Bonjour

1. BON LIVRAISON

.. je souhaiterais apporter, je modifie simplement le modèle, c’est bien ça ?

Oui c'est exact

1. Serait-il possible que la macro ne s’execute à chaque fois que pour les nouvelles lignes, car je serai amené à alimenter le tableau et si à chaque lancement de la macro, toutes les lignes sont prises en compte ce sera pas très lisible et je vais plus me retrouver

Cela peut se faire mais il faut un repère. Par exemple on peut ajouter un X dans la feuille Stock_In en colonne F, si le bon a été généré. Votre avis ?

2. Serait-il possible d’avoir en J3 une info type « Numéro de palette / Nombre total de palettes » dans notre exemple ça donnerait

Oui. Pour que ce soit facile on pourrait trier afin que les BL soient classés par ordre croissant. Cela se ferait par code bien entendu. Ok pour vous ?

2. SAUVE PDF

Si je veux modifier le chemin pour l’enregistrement, est-ce que ma modification est bonne, ponctuation incluse

Oui c'est tout à fait juste.

Par contre pour l'erreur, j'ai testé avant et cela fonctionne. Vérifiez que le format votre date est en J1 inclut bien les tirets entre le jour, mois et année. et pas de slash --> "/"

Re,

Cela peut se faire mais il faut un repère. Par exemple on peut ajouter un X dans la feuille Stock_In en colonne F, si le bon a été généré. Votre avis ?

Oui, c'est parfait.

Oui. Pour que ce soit facile on pourrait trier afin que les BL soient classés par ordre croissant. Cela se ferait par code bien entendu. Ok pour vous ?

Je n'ai pas très bien compris...En fait, ce qui est important pour moi c'est de savoir a une date donnée combien de palettes ont été envoyées. Dans notre cas, le 26/11 trois palettes ont été envoyées, si ce que tu préconises va dans le même sens alors c'est parfait. En d'autres termes, la date est importante après l'ordre l'est moins, tant que je sais combien de palettes ont été envoyées et qu'elles sont numérotées

Par contre pour l'erreur, j'ai testé avant et cela fonctionne. Vérifiez que le format votre date est en J1 inclut bien les tirets entre le jour, mois et année. et pas de slash --> "/"

J'ai modifié le format de cellule, j'ai mis "dd-mm-yyyy" et le même message d'erreur apparait

Je n'ai pas très bien compris...En fait, ce qui est important pour moi c'est de savoir a une date donnée combien de palettes ont été envoyées. Dans notre cas, le 26/11 trois palettes ont été envoyées, si ce que tu préconises va dans le même sens alors c'est parfait. En d'autres termes, la date est importante après l'ordre l'est moins, tant que je sais combien de palettes ont été envoyées et qu'elles sont numérotées

Au vu de ton message, mettons que j'ai 4 lignes complétée au 26/11, 3 lignes avec le BL# comme dans le fichier mais aussi 1 ligne de plus avec un numéro de BL différent. Là J3 doit mentionner quoi ? pour 3 lignes --> 1/3, 2/3 et 3/3 et pour 1 ligne 1/1 ?

Pour le PDF, cela fonctionne chez moi. Fais ce test en remplaçant la ligne complète par --> MsgBox Chemin & Sheets(i).Name & "_" & Sheets(i).Range("J1") & ".pdf"
Dis moi ce que cela te renvoie comme info

Hello Dan,

Au vu de ton message, mettons que j'ai 4 lignes complétée au 26/11, 3 lignes avec le BL# comme dans le fichier mais aussi 1 ligne de plus avec un numéro de BL différent. Là J3 doit mentionner quoi ? pour 3 lignes --> 1/3, 2/3 et 3/3 et pour 1 ligne 1/1 ?

Bien vu ! c'est exact. En théorie, je ne fais qu'un envoi par journée, mais il se pourrait qu'un deuxième envoi soit envisage.

Pour le PDF, cela fonctionne chez moi. Fais ce test en remplaçant la ligne complète par --> MsgBox Chemin & Sheets(i).Name & "_" & Sheets(i).Range("J1") & ".pdf"

Dis moi ce que cela te renvoie comme info

Ca m'affiche une MsgBox en me disant que C:\Users\monnom\Telechargement\BL1_26/11/2020.pdf etc... je pense que tu avais bien vu dès le départ avec ces "/" mais j'ai bien modifié le format de date dans ma feuille BL (dd.mm.yyyy) et quand je génère les BL avec le bouton BON LIVRAISON, je vois bien que tous mes BL sont au format 26.11.2020...étrange ! Je viens de voir (juste avant d'envoyer le message sur le forum) que dans les BL génèrés en J1 j'ai bien 26.11.2020 mais dans la barre de formule j'ai 26/11/2020

Aussi, si ce n'est pas trop, pourrait-on regrouper les BL en une seul fichier pdf avec comme nom "BL - Numéro du BL" dans notre exemple BL - AB20-01 qui contiendrait les 3 feuilles 2 pour ABCD025 et 1 pour ABCD020.

J'ai rajouté une colonne F pour savoir si le bon a été émis, du coup peut-on s'affranchir et supprimer la colonne E car ca sera toujours 1 finalement

Merci

Bonjour

En théorie, je ne fais qu'un envoi par journée, mais il se pourrait qu'un deuxième envoi soit envisage.

On pourrait alors sauver le fichier sous un nom comme ceci --> BL_AB020_Date_heure. Exemple --> BL_20201205_092010 (20201205 = 05/12/2020 et 092010 = 09h 20min 10sec). Pour le numéro du bon AB020 ou autre, cela suppose qu'il n'y a toujours que 5 caractères (on fait abstraction de l'indice 01, 02 après ce numéro)

Aussi, si ce n'est pas trop, pourrait-on regrouper les BL en une seul fichier pdf avec comme nom "BL - Numéro du BL" dans notre exemple BL - AB20-01 qui contiendrait les 3 feuilles 2 pour ABCD025 et 1 pour ABCD020

Oui c'est faisable mais en évitant l'indice 01, et en le remplaçant par la proposition ci-dessus (avec heure, minute, seconde). Cela évite d'avoir un bug si vous sauvegardez deux fois la même chose ou le même jour car mettre un indice derrière BL-AB020 est plus compliqué. Il faudrait aller vérifier que le fichier n'existe pas déjà dans le répertoire de sauvegarde.

Hello Dan,

On pourrait alors sauver le fichier sous un nom comme ceci --> BL_AB020_Date_heure. Exemple --> BL_20201205_092010 (20201205 = 05/12/2020 et 092010 = 09h 20min 10sec). Pour le numéro du bon AB020 ou autre, cela suppose qu'il n'y a toujours que 5 caractères (on fait abstraction de l'indice 01, 02 après ce numéro)

En fait, ma numération de BL est la suivante AB20-01

AB > nom de la compagnie

20 > l'année 2020

-01 > numéro de bon en 2020

Donc mon 5e bon en Janvier 2021 sera AB21-05 etc...

Si tu as une autre suggestion pour les numéroter je suis preneur, je viens de commencer

Sans vouloir t'embêter (mais on est là aussi pour apprendre ) si le numéro de bon est unique, il n'y a à priori pas de risque de doublons

Si c'est plus simple à réaliser ce que tu préconises, vas-y

Merci Dan

Non le souci c'est que l'on peut sauver dans un seul pdf, des bons avec la référence AB20-01 et AB20-02 (exemple dans le fichier) et que là je ne comprends pas pourquoi sauvez sous le nom BL_AB20-01 sachant que dans le pdf il y aura les bom AB20-01 et AB20-02.

Exemple : Si j'ai deux bons AB20 que je sauve dans la journée. Le premier va s'appeler BL_AB20-01. je ferme le fichier puis je décide de sauver une deuxième bon AB20, Je fais comment pour savoir qu'il doit avoir l'extension AB20-02 ?

D'où l'idée d'utiliser la date et l'heure.

Faut garder en tête qu'une fois imprimé, on ne sait pas si le PDF a été généré. On peut le différer... ou alors le fait d'imprimer inclut automatiquement la génération du pdf mais ce n'était pas l'idée puisque l'on a un 3ieme bouton

D'accord Dan, allons-y pour la date et l'heure :)

Le fichier en retour pour test

Il faudra déterminer le répertoire de sauvegarde dans la variable Chemin de la macro qui génère le Pdf

Le nom du fichier sauvegardé sera --> BL_ + les 4 premiers caractères du bon (nom de compagnie et année) + date inversée + heure

Super Dan !

On se rapproche de la fin, presque tout est bon.

- Cela ne génère que quand il n'y a pas de X dans la colonne F, le fichier est bien sauvegardé comme il faut

Mes deux retours :

Finalement, est-il possible de supprimer la colonne E (je voudrais pas casser les macros) ?

Par rapport, à l'histoire de " Numéro de Palette / Nombre Total de Palette " en cellule J3

que tu avais si bien résumé

Au vu de ton message, mettons que j'ai 4 lignes complétée au 26/11, 3 lignes avec le BL# comme dans le fichier mais aussi 1 ligne de plus avec un numéro de BL différent. Là J3 doit mentionner quoi ? pour 3 lignes --> 1/3, 2/3 et 3/3 et pour 1 ligne 1/1 ?

C'est exact

Merci Dan

Finalement, est-il possible de supprimer la colonne E (je voudrais pas casser les macros) ?

On peut supprimer la colonne E mais la cellule J3 va contenir quoi dès lors ?

Edit : si on supprime la colonne E, il faut adapter trois lignes dans le code Bon_livraison :

1. A cette ligne If UCase(cel.Offset(0, 4)) <> "X" Then il faut remplacer le 4 par 3
2. Sur la ligne .Range("J3") = cel.Offset(0, 3) 'Quantité soit Inhiber en mettant une apostrophe devant le point juste avant Rangeou si on veut avoir toujours 1 par défaut, on remplace cette ligne par ceci --> .Range("J3") = 1
3. A ligne bon emis remplacer le 4 par 3