Création intelligente auto nouvelles lignes avec intégration de formules

Bonjour tout le monde !!!

A l'impossible, nul n'est tenu... mais je sais qu'avec Excel le champs des possibles est parfois très large.

Voici ma problématique : (Tableau modèle en, PJ).

A partir de la ligne 35, Est-il possible, que les nouvelles lignes de mon tableau se créent automatiquement dès la ligne précédente est remplie (notamment sur les cellules de la colonne G).

Est-il possible que ces nouvelles lignes crées automatiquement intègrent des formules de calcul ? (les mêmes que sur les lignes précédentes)

Enfin est-il possible que ces nouvelles lignes crées ne perturbent pas l'actuelle dernière ligne du tableau qui calcule tous les totaux.

Si tout cela est possible, quel en est le chemin pour y parvenir ?

Je vous remercie beaucoup de votre aide... et vous félicite pour les nombreuses solutions dans l'utilisation de ce fabuleux logiciel !!!

284fdcpour-formule.xlsm (27.06 Ko)

Bonjour Parfix,

Sur ce forum, le possible est déjà fait, l'impossible est en cours, et pour les miracles prévoir 48h de délais ! ;)

Plaisanterie mise à part, pourquoi les nouvelles lignes doivent-elles être créées automatiquement qu'à partir de la ligne 35 ?

Car j'ai un tableau secondaire à droite du premier tableau, avec d'autres informations...

Ce tableau secondaire s'arrête à la ligne 32 et lui n'a pas besoin d'évoluer.

En revanche, le 1er tableau peut aller jusqu'à 250 lignes certains jours et 40 à d'autres...

Bonjour,

Oui c'est tout à fait possible, ce qui est décrit ici c'est un tableau nommé, des formules qui s'ajoutent automatiquement à chaque nouvelle ligne, un sous total en fin de tableau.

Si on prend une solution manuelle où il sera obligatoire soi-même de faire un clic droit pour ajouter une ou plusieurs nouvelles lignes qui ne seront pas à la même hauteur que les précédentes, pas besoin de voir plus loin, je joins le résultat. Pour avoir un tableau nommé il faut sélectionner la zone du tableau et aller dans "mettre sous forme de tableau" dans "Accueil"

Bonjour,

Je vous soumets également ma proposition car j'avais commencé à me pencher sur votre besoin.

Je comprends aussi que cette feuille sera dupliquée quotidiennement car elle correspond aux encaissements journaliers. A cet effet, la macro est renseignée dans le module objet du fichier (et non dans un module simple) : ainsi, toutes les feuilles créées dans le fichier Excel et intitulées "Saisie des opérations" en A1 seront concernées par cette automatisation. A défaut, la macro ne se déclenchera pas.

L'automatisation répond aux conditions de votre demande dès que l'avant-dernière ligne est complétée. Une ligne est alors insérée en dessous et les formules de la dernière ligne du tableau sont copiées.

529fdcpour-formule2.xlsm (32.65 Ko)

Avant toute chose, AntoineDL et Ausecour, je vous adresse un immense MERCI pour vous être penché sur ma problématique.

Ensuite, je vous adresse à l'un et à l'autre un grand BRAVO pour être parvenu à de vraies solutions et différentes en plus.... je suis sincèrement bluffé par votre maitrise de ce logiciel souvent si opaque pour moi... j'avoue que vos propositions, à mes yeux, c'est un peu comme de la magie... ça paraît incroyable, pourtant bien réel, mais on ne comprend vraiment pas le truc !!!

Malgré tout, comme j'adore comprendre, et surtout j'aimerais pouvoir utiliser vos solutions avec aisance, et pourquoi pas les faire évoluer, pouvez-vous m'éclairer sur certains aspects.

A l'attention d'AUSECOURS :

A propos de ta solution manuelle.

Même si je ne vais probablement pas utiliser cette soluce car la contrainte d'une intervention manuelle avec de multiples utilisateurs qui ne maîtrisent,pour beaucoup, pas suffisamment l’informatique, est trop risquée, j'aimerais malgré tout comprendre comment tu en es arrivé à ce résultat que je trouve vraiment super. Quand je vais dans les formules de calcul que j'avais crées, je réalise que tu les as quasi toutes transformées... mais je ne parviens pas plus du tout à les comprendre... Si tu peux me raconter ton process, c'est top !!!

A propos de ta solution avec les tableaux nommés.

Peux-tu m'expliquer comment cela fonctionne ? Comment, à partir du moment au j'ai crée mon tableau nommé avec la fonction "Mettre sous forme de tableau", j'arrive à dire au logiciel de créer et/ou insérer des nouvelles lignes automatiquement si besoin ? et que ces mêmes lignes intègrent les formules comme les autres ? Si tu peux m'expliquer le procédure précisément, c'est canon.

A l'attention d'Antoine DL :

A propos de ta solution avec macro :

Je ne comprends pas comment cela marche, mais ça semble fonctionner comme espéré !!!

Questions malgré tout :

L'utilisation de cette macro comporte-t-elles des contraintes ou bien des conditions d'utilisations particulières
Lorsque je tape un chiffre dans une cellule qui fait apparaître une nouvelle ligne, je n'ai plus accès à la fonction CTRL Z, notamment pour effacer ce que je viens de taper. Comment cela s'explique ? s'évite ?
Quel est le principe de ta macro ? quel lui a tu demandé en fait ?

Enfin, comment je dois faire pour transposer ta macro sur mon fichier master ?

Merci beaucoup pour ton retour.

Encore un grand merci à tous les 2 !!!

Alexandre

Bonjour à tous

Merci de compléter ton profil en indiquant ta version Excel afin qu'on ne parte pas inutilement sur de solutions non compatibles avec celle-ci

Que l'on automatise ou non l'ajout de lignes (cliquer sur un bouton pour démarrer un code ou faire un clic droit Insérer ligne fait une manipulation dont le code n'est pas forcément utile)

Dans les 2 cas il est recommandé de travailler en tableau structuré (disponibles dans Excel depuis 17 ans pour justement gérer plus facilement et efficacement les listes de données).

Le plus simple est de placer les totaux au dessus du tableau et non en dessous.
Il suffit alors de saisir sous la dernière ligne du tableau pour que la saisie soit automatiquement intégrée dans le tableau avec formules et mises en formes et impacte les totaux...

La question évoquée par AntoineDL est intéressante : au delà de la journée de saisie, que fait-on du classeur ?

Si des feuilles doivent être ajoutées il faut un modèle vierge. Celui-ci peut être une feuille du classeur recopiée manuellement ou par code insérée depuis un modèle de feuille (xltx)

Sinon à partir de 2010, il est possible d’utiliser un classeur journalier et la synthèses mensuelle sera faite par PowerQuery

Bonjour,

@Parfix :

Je vous joins le projet légèrement modifié avec une simplification qui n'affecte pas le résultat souhaité.

208fdcpour-formule3.xlsm (28.58 Ko)

L'utilisation de cette macro comporte-t-elles des contraintes ou bien des conditions d'utilisations particulières

Non aucune condition particulière. La macro peut être insérée directement dans votre fichier Master.

La seule petite contrainte est que la macro profite de la particularité suivante d'Excel concernant la mise à jour des totaux : Lorsqu'une ligne est insérée après la dernière ligne d'un tableau, Excel ne met pas à jour la plage dans la fonction SOMME. Lorsqu'une ligne est insérée après l'avant dernière ligne d'un tableau, Excel met à jour la plage dans la fonction SOMME. Raison pour laquelle l'automatisation répond aux conditions de votre demande dès que l'avant-dernière ligne est complétée.

Après il est toujours possible de saisir jusqu'à la dernière ligne et modifier la macro pour actualiser la formule des totaux. J'ai souhaité faire simple en utilisant la particularité d'Excel présentée ci-dessus.

Lorsque je tape un chiffre dans une cellule qui fait apparaître une nouvelle ligne, je n'ai plus accès à la fonction CTRL Z, notamment pour effacer ce que je viens de taper. Comment cela s'explique ? s'évite ?

Sur le site de Microsoft. Il est dit que la fonction annulation CTRL+Z "est prévue pour les actions effectuées par l'utilisateur". Or les actions d'une macro ne correspondent pas à une une action d'un utilisateur. Il n'y a donc rien à annuler.

Une macro pouvant impacter n'importe quel objet (une cellule, une feuille, un classeur, une application, etc.) et votre demande initiale étant de créer automatiquement des lignes sur une feuille qui sera dupliquée quotidiennement, la macro est déclenchée systématiquement lorsqu'une des cellules du classeur est modifiée. Selon les conditions fixées, elle s'exécute jusqu'à son terme en produisant des effets (insertion d'une ligne et maj des formules) ou s'arrête immédiatement si les conditions ne sont pas remplies.

Entre le moment où vous saisissez une valeur dans une cellule et le moment où vous continuez la saisie du tableau, la macro s'est donc exécutée pour identifier si une ligne doit être insérée ou pas. Cette dernière action ne peut être annulée par la fonction annulation CTRL+Z.

Quel est le principe de ta macro ? quel lui a tu demandé en fait ?

Enfin, comment je dois faire pour transposer ta macro sur mon fichier master ?

Il vous suffit d'aller dans l'éditeur de macro (Alt+F11), d'ouvrir l'objet ThisWorkbook dans l'explorateur de projets, de copier la macro, et de la coller dans l'objet ThisWorkbook de votre fichier Master.

A propos de ta solution manuelle.

Même si je ne vais probablement pas utiliser cette soluce car la contrainte d'une intervention manuelle avec de multiples utilisateurs qui ne maîtrisent,pour beaucoup, pas suffisamment l’informatique, est trop risquée, j'aimerais malgré tout comprendre comment tu en es arrivé à ce résultat que je trouve vraiment super. Quand je vais dans les formules de calcul que j'avais crées, je réalise que tu les as quasi toutes transformées... mais je ne parviens pas plus du tout à les comprendre... Si tu peux me raconter ton process, c'est top !!!

A propos de ta solution avec les tableaux nommés.

Peux-tu m'expliquer comment cela fonctionne ? Comment, à partir du moment au j'ai crée mon tableau nommé avec la fonction "Mettre sous forme de tableau", j'arrive à dire au logiciel de créer et/ou insérer des nouvelles lignes automatiquement si besoin ? et que ces mêmes lignes intègrent les formules comme les autres ? Si tu peux m'expliquer le procédure précisément, c'est canon.

Bonjour,

pas de souci je vais donc partir sur plusieurs explications:

Pour créer un tableau nommé et transformer les formules, avoir une ligne de totaux etc... tu as compris comment commencer, à savoir mettre sous forme de tableau ton tableau via la fonction Excel prévue à cet effet, ce qu'il faut comprendre c'est ce qu'il se passe à ce moment là. Quand tu transforme ton tableau en tableau nommé, tous ses champs ont des noms, ainsi que le tableau qui est appelable par celui-ci, il possède également une mise en forme commune pour chacune de ses lignes, si il n'y a pas de ligne de totaux, on peut automatiquement rajouter des lignes en écrivant directement sous le tableau. Pour avoir une nouvelle ligne de totaux j'ai simplement coché "ligne total" dans l'onglet création de tableau qui s'affiche dès qu'on clique sur une cellule du tableau nommé, j'ai ensuite renseigné les calculs que je voulais faire en mettant somme partout.

Pour les formules la conversion était très simple, si je voyais B3 dans la formule, je sélectionnais cette partie de la formule puis cliquait à nouveau sur la cellule B3 pour remplacer B3 par un nom plus approprié pour le tableau nommé, elles sont toujours les même, mais elles utilisent simplement des champs nommés à présent.

Pour les formules qui s'ajoutent à chaque nouvelle ligne, c'est déjà géré par les tableaux nommés, quand tu as une seule et même formule sur un champ, ça va considérer que cette formule doit être appliquée sur toutes ses lignes à venir et va donc le faire à chaque ajout de ligne.

Pour ajouter de nouvelles lignes on peut choisir de faire de plusieurs façons, celle que je préfère c'est de revoir la structure de la feuille, afin de ne plus rien avoir en dessous du tableau qui aura de nouvelles lignes automatiquement, ça ne nécessite pas de macros. Pour parvenir à ça j'ai créé une nouvelle ligne de total en copiant celle avec les formules existantes en haut du tableau, puis j'ai décoché "ligne total", pour la partie de synthèse à la fin je l'ai mise tout en haut du fichier et groupé les lignes qui la contient pour la masquer/afficher à souhait. Je joins le résultat, si quelque chose n'est toujours pas clair je pourrai expliquer.

Oh j'allais oublié, avec ce format il suffit d'écrire une information sous la dernière ligne du tableau et elle sera automatiquement ajoutée à celui-ci!

Bonsoir Parfix, le Forum,

Un exemple avec un principe différent. Voir notes en rouge sur la 1ière feuille.

Une feuille modèle est ajoutée et permet par copie de celle-ci d'utiliser le code au niveau worksheet.

L'impression des opérations saisies ne portera que sur les lignes affichées.

97fdcmacro.xlsm (96.70 Ko)

@78Chris

Merci beaucoup pour les éléments que tu me proposes avec l'outil tableau... Il faut que je me penche sur les tuto concernés car je n'ai pas été formé à cette école !!!

Que veux-tu dire par "Si des feuilles doivent être ajoutées il faut un modèle vierge. Celui-ci peut être une feuille du classeur recopiée manuellement ou par code insérée depuis un modèle de feuille (xltx)" ?

Pour ce qui est de l'utilisation ultérieur des classeurs journaliers, cela sert à remplir des colonnes de synthèse, journalières , hebdomadaires, mensuelles et même annuelles... tout cela pour la comptabilité...mais je dois avouer que que c'est mon épouse qui s'occupe de cette partie là... et je ne sais pas trop comment elle procède, mais je pense, avec beaucoup de ressaisie manuelles. j'imagine qu'il doit y avoir des possibilités innombrables de création d’outils via excel... mais qui m'échappe complétement.

"Sinon à partir de 2010, il est possible d’utiliser un classeur journalier et la synthèses mensuelle sera faite par PowerQuery".

Que cela me ^permettrait-i l? Tu aurais des tuto à me conseiller sur ce thème ?

Merci beaucoup Chris pour ton implication !

@Ausecours

Tes explications sont limpides et merci beaucoup d'avoir pris le temps de me les transmettre.

Ta méthode est relativement simple... quand on connait un peu mieux l'envers du décors !!!

je ne manquerais pas de revenir vers toi si besoin !!!

Mille mercis !!!

@AntoineDL

Bravo pour ta solution !!! Elle est je pense celle qui que je vais adopter à terme... on dirait presque de la magie !!!

J'ai réussi à copier et utiliser ta macro dans mon fichier master, et à l'usage j'ai réalisé que je pouvais même l'utiliser bien avant la ligne 35, et ce, sans mettre en péril mon tableau de droite, car ta macro n'agit pas par insertion ; ce qui me décalerait mon tableau de droite mais bien par création d'une nouvelle ligne ! Franchement brillant.

Pour arriver à ce résultat j'ai donc modifié ta macro en lui demandant d'être active à partir de la ligne 3 au lieu de la ligne 35, et cela fonctionne à un détail près : lorsque je lance l’exécution de la macro, je pars avec 3 lignes de base déjà crées. Or dès que je remplis un chiffre qui déclenche la macro pour la création de la ligne 4, cette dernière est bien créée mais son numéro (colonne A) de ligne est à nouveau le 3 et non le 4. Ce n'est qu'à partir de la ligne suivante que l'incrémentation se fait normalement.

Si tu as une astuce pour contourner ce problème, je suis carrément preneur !

Encore merci et bravo tes propositions !!!

@X Cellus

Franchement, vous êtes incroyables sur ce forum !!! Encore une solution totalement inédite à ma problématique, pour un résultat similaire !

Ta solution est réellement intéressante, j'adore !!! Je vais quand malgré tout creuser celle d'Antoine, qui pourrait me permettre partir sur les création automatiques de mes ligne quasiment dès le début de mon doc sans attendre la ligne 35 et sans risquer de mettre en péril le tableau de droite !!!

Si tu penses à une autre solution qui utilise le démasquage progressif, mais avec cette nouvelle contrainte, je suis aussi preneur !!!

merci aussi à toi pour ta gentille participation

lorsque je lance l’exécution de la macro, je pars avec 3 lignes de base déjà crées. Or dès que je remplis un chiffre qui déclenche la macro pour la création de la ligne 4, cette dernière est bien créée mais son numéro (colonne A) de ligne est à nouveau le 3 et non le 4. Ce n'est qu'à partir de la ligne suivante que l'incrémentation se fait normalement.

Je n'ai rien compris :)

Si vous souhaitez "raccourcir" le tableau de gauche, je vous propose de partir sur le format en pièce jointe. Il ne semble pas poser de problème dans l'incrémentation du numéro d'opération.

Pour rappel, l'automatisation s'exécute lorsque l'avant-dernière ligne du tableau est complétée, afin de conserver la formule correcte dans les totaux.

@Antoine

Tout fonctionne à Merveille !!! Bravo et merci encore !!!

Petite question subsidiaire d'ailleurs : lorsqu'une 50aine de lignes sont crées sur un des classeurs par ex et je souhaite repartir sur une feuille vierge à 3 lignes, quel process me conseillez-vous pour notamment préserver les formules ?

Merciiiiii

@Antoine

Une petite question aussi... J'ai pour habitude de verrouiller les cellule avev formule de calcul pour éviter que les néophytes mettre trop de bazar, cette macro peut-elle fonctionner avec une feuilles et cellules protégée ?

Petite question subsidiaire d'ailleurs : lorsqu'une 50aine de lignes sont crées sur un des classeurs par ex et je souhaite repartir sur une feuille vierge à 3 lignes, quel process me conseillez-vous pour notamment préserver les formules ?

Le plus simple est de prévoir dans votre fichier Master une feuille vierge correspondant à la feuille du dernier fichier transmis (fdcpour-formule4.xlsm).

Il suffirait ainsi de mettre une date identique et de faire commencer le numéro d'opération à 51 au lieu de 1. Dans vos tableaux de contrôle de caisse, la trésorerie à la clôture des 50 premières opérations deviendraient l'ouverture des 50 opérations suivantes, etc. Y-a-t-il un intérêt autre que pour l'impression ? Vous pourriez gérer par une mise en page.

J'ai pour habitude de verrouiller les cellule avev formule de calcul pour éviter que les néophytes mettre trop de bazar, cette macro peut-elle fonctionner avec une feuilles et cellules protégée ?

Dans ma fonction, nous responsabilisons les collaborateurs. C'est toujours plus profitable que de procéder à un verrouillage systématique.

En l'état, la macro ne peut fonctionner avec des feuilles et des cellules protégées.

Il faudrait alors prévoir dans la macro : déverrouillage des feuilles (avec ou sans mot de passe) > insertion des nouvelles lignes > reverrouillage des feuilles (avec ou sans mot de passe).

Bien évidemment, le mot de passe serait renseigné dans la macro. Il faudrait aussi verrouiller par un mot de passe l'affichage de la macro pour éviter qu'il soit lisible par une personne qui accède à l'éditeur de macros.

Antoine,

Je dois avouer que j'ai fini par devoir verrouiller les cellules de calcul car j'ai eu beaucoup trop de collaborateurs qui paralysaient tout le système d'encaissement à cause de mauvaises manip dans les cases de formules, d'où la nécessité que j'ai eu de les protéger, et j'ai même mis en couleur les seules cases où ils peuvent intervenir.

Crois-tu que tu pourrais me rajouter dans la macro du fichier fdcpour-formule4.xlsm, le système de déverrouillage-Insertion-reverouillage... car j'en suis bien incapable.

Pour le mot de passe, un simple 1234 me suffira... (c'est dejà mon code actuel)... mais il ne s'agit pas d'un code secret c'est juste une protection pour les maladresses des utilisateurs...

Donc ce n'est pas nécessaire non plus d'envisager de "verrouiller par un mot de passe l'affichage de la macro pour éviter qu'il soit lisible par une personne qui accède à l'éditeur de macros"...

Dis moi si c'est possible pour toi... et vraiment un immense merci de t'être penché sur mon cas !!!

Bonjour,

Ci-joint avec la fonction ajoutée.

Rechercher des sujets similaires à "creation intelligente auto nouvelles lignes integration formules"