Microsoft 365 Copier des données par VBA dans différentes feuilles

[Aloha]

Bonjour,

Dans une situation réelle, représentée par l'exemple ci-joint, un administrateur distribue des avances à des agents (P1 à P3).
Il inscrit les avances distribuées dans la feuille "Avances".

Ce que je recherche:
un bout de code VBA qui copie, chronologiquement dans les feuilles P1 à P3, les avances distribuées, comme je l'ai fait manuellement, de préférence automatiquement, càd que dès que l'administrateur saisit une avance dans la feuille "Avances" elle est copiée de suite dans la feuille respective..

Merci d'avance pour toute aide
Aloha

 

[Jacky67]

Bonjour,

Dans une situation réelle, représentée par l'exemple ci-joint, un administrateur distribue des avances à des agents (P1 à P3).
Il inscrit les avances distribuées dans la feuille "Avances".

Ce que je recherche:
un bout de code VBA qui copie, chronologiquement dans les feuilles P1 à P3, les avances distribuées, comme je l'ai fait manuellement, de préférence automatiquement, càd que dès que l'administrateur saisit une avance dans la feuille "Avances" elle est copiée de suite dans la feuille respective..

Merci d'avance pour toute aide
Aloha

Bonjour,
Ceci placé dans le ThisWorkbook devrait faire
La mise à jour se fait à la sélection des feuilles

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim Plage, Derlg&
    If Sh.Name <> "Avances" Then
        Application.ScreenUpdating = False
        Sh.Cells.Clear
        With Sheets("Avances")
            If .FilterMode Then .ShowAllData
            Derlg = .Cells(.Rows.Count, "b").End(xlUp).Row
            Set Plage = .Range("$A$2:$C" & Derlg)
            Plage.AutoFilter Field:=2, Criteria1:=Sh.Name
            Plage.SpecialCells(xlCellTypeVisible).Copy Sh.[a1]
            Sh.Columns(2).Delete
            .AutoFilterMode = False
        End With
    End If
End Sub

 

[Aloha]

Bonsoir,

Merci beaucoup.
Dans le fichier exemple ton code fonctionne. La seule chose à modifier: lorsque j'efface les avances dans "AVANCES", les bordures des cellules N12:O19 dans les feuilles individuelles doivent rester, ainsi que le total en-dessous.
Cela fait deux jours que je suis en train de rédiger la réponse. J'ai mis autant de temps parce qu'il a fallu que je retravaille le fichier. Le résultat est un fichier modifié.

J'ai maintenant deux tâches:

1. dans une première étape adapter le code au fichier réel

Seulement, je me rends compte que, et je le regrette, pour la fabrication de mon fichier exemple, je ne suis pas resté assez fidèle au fichier réel, où les données sont stockées dans d'autres endroits et où il y a d'autres feuilles qui, elles, ne sont pas concernées et sont donc à exclure.
J'avais espéré comprendre le code assez pour pouvoir l'adapter, ce qui malheureusement n'est pas le cas.
Dans le fichier réel il y a davantage de personnes. J'en ai donc ajouté dans le fichier exemple et je me suis rendu compte qu'il fonctionne aussi pour ces personnes supplémentaires. C'est bien.
Comment faut-il modifier le code pour qu'il tienne compte de la situation réelle, illustrée par le fichier ci-joint?

Observation "en marge": je ne comprends pas ce qui fait que, lorsque je crée une feuille vide supplémentaire, dans A1 et B1 apparaissent automatiquement par coup de baguette magique Date et Montant.

2. dans une deuxième étape:

Mon fichier pour lequel tu as rédigé le code concerne la distribution des avances qui sont inscrites dans une plage centrale et que le code inscrit dans les fiches respectives.

A part les avances, il y a les dépenses engagées par les différents agents et qui doivent être copiées ensemble dans la feuille "TOUT" du fichier ci-joint.

Il s'agit de copier les données des colonnes U à AC de chaque personne dans le fichier "TOUT", en tant que valeurs.
Je pense que cette tâche ne doit pas être trop difficile: il faut déterminer les feuilles à copier: toutes les feuilles à part "LISTES", "AVANCES", et "TOUT" et copier les plages de données les unes en-dessous des autres, si possible avec bordures et les totaux en bas comme dans "TOUT" dans le fichier joint.

P.S.: le code est assez "dangereux": je l'avais copié dans Workbook du fichier joint. Lorsque j'ai sélectionné une feuille individuelle tout le contenu a disparu et en haut à gauche a apparu le texte "Prélèvement des chèques"

Bonne fin de dimanche
Aloha

 

[Jacky67]

Bonsoir,

Merci beaucoup.
Dans le fichier exemple ton code fonctionne. La seule chose à modifier: lorsque j'efface les avances dans "AVANCES", les bordures des cellules N12:O19 dans les feuilles individuelles doivent rester, ainsi que le total en-dessous.
Cela fait deux jours que je suis en train de rédiger la réponse. J'ai mis autant de temps parce qu'il a fallu que je retravaille le fichier. Le résultat est un fichier modifié.

J'ai maintenant deux tâches:

1. dans une première étape adapter le code au fichier réel

Seulement, je me rends compte que, et je le regrette, pour la fabrication de mon fichier exemple, je ne suis pas resté assez fidèle au fichier réel, où les données sont stockées dans d'autres endroits et où il y a d'autres feuilles qui, elles, ne sont pas concernées et sont donc à exclure.
J'avais espéré comprendre le code assez pour pouvoir l'adapter, ce qui malheureusement n'est pas le cas.
Dans le fichier réel il y a davantage de personnes. J'en ai donc ajouté dans le fichier exemple et je me suis rendu compte qu'il fonctionne aussi pour ces personnes supplémentaires. C'est bien.
Comment faut-il modifier le code pour qu'il tienne compte de la situation réelle, illustrée par le fichier ci-joint?

Observation "en marge": je ne comprends pas ce qui fait que, lorsque je crée une feuille vide supplémentaire, dans A1 et B1 apparaissent automatiquement par coup de baguette magique Date et Montant.

2. dans une deuxième étape:

Mon fichier pour lequel tu as rédigé le code concerne la distribution des avances qui sont inscrites dans une plage centrale et que le code inscrit dans les fiches respectives.

A part les avances, il y a les dépenses engagées par les différents agents et qui doivent être copiées ensemble dans la feuille "TOUT" du fichier ci-joint.

Il s'agit de copier les données des colonnes U à AC de chaque personne dans le fichier "TOUT", en tant que valeurs.
Je pense que cette tâche ne doit pas être trop difficile: il faut déterminer les feuilles à copier: toutes les feuilles à part "LISTES", "AVANCES", et "TOUT" et copier les plages de données les unes en-dessous des autres, si possible avec bordures et les totaux en bas comme dans "TOUT" dans le fichier joint.

P.S.: le code est assez "dangereux": je l'avais copié dans Workbook du fichier joint. Lorsque j'ai sélectionné une feuille individuelle tout le contenu a disparu et en haut à gauche a apparu le texte "Prélèvement des chèques"

Bonne fin de dimanche
Aloha

Re..
Il y a des incohérences dans les colonnes
Plage==> U:AC puis==> U:AD
Doublon de colonne "Agents" sur les feuilles et dans "tout"
Avant de continuer merci de rectifier avec une structure définitive.

 

[Aloha]

Bonjour,
Désolé pour ces fautes.
Voici le fichier rectifié, exempt d'erreurs, j'espère.
Avant de continuer, j'ai envoyé le fichier à la responsable de la comptabilité pour lui demander si la structure du fichier est ok pour elle. J'attends sa réponse.
Bonne journée
Aloha

 

[Jacky67]

Bonjour,
Désolé pour ces fautes.
Voici le fichier rectifié, exempt d'erreurs, j'espère.
Avant de continuer, j'ai envoyé le fichier à la responsable de la comptabilité pour lui demander si la structure du fichier est ok pour elle. J'attends sa réponse.
Bonne journée
Aloha

Re...
Dans le classeur en Pj
Un nom est créé [Agents] en fonction de la liste des agents de la feuille "Listes" colonne F
Le fonctionnement du classeur est basé sur ce nom
Les codes se trouvent dans le ThisWokbook et dans le module de la feuille "TOUT"
Les mises à jour se font à la selection des feuilles

 

[Aloha]

Bonjour,

Ta solution est formidable!
Seulement, je n'ai pas encore de feedback de la part de la responsable (j'aurais dû l'attendre avant de poster le fichier).

Un nom est créé [Agents] en fonction de la liste des agents de la feuille "Listes" colonne F

Je ne comprends pas ce que tu veux dire. Ce nom est créé où? Ce qui serait utile ce serait qu'une feuille à ce nom soit créée (depuis la feuille MODELE que je viens de créer et qui est masquée) dès qu'il est ajouté dans la liste.

Ce qui n'est pas comme il faut -mais ça n'a rien à voir avec le code VBA- c'est la numérotation des dépenses. Lorsqu'on regarde dans TOUT on se rend compte que ça ne peut pas fonctionner de la sorte, puisque les numéros se répètent autant de fois qu'il y a d'agents. Mais, je me répète, ça n'a rien à voir avec le code. Je vais voir avec la responsable.

Si je ne risquais pas d'abuser de ta générosité, je proposerais une ajoute au code: trier TOUT à chaque fois que la feuille est sélectionnée, d'après la date, puis l'agentréduire le formatage à l'aire contenant les valeurs, et placer les totaux en-dessous. Pour pousser plus loin encore on pourrait faire faire la mise en page et prévoir un bouton pour imprimer.

Mais, je me répète: je ne veux pas abuser!

Jeudi je verrai la responsable et je vais finaliser avec elle la structure du fichier.

Bonne journée
Aloha

 

[Jacky67]

Bonjour,


Seulement, je n'ai pas encore de feedback de la part de la responsable (j'aurais dû l'attendre avant de poster le fichier).
Je ne comprends pas ce que tu veux dire. Ce nom est créé où? Ce qui serait utile ce serait qu'une feuille à ce nom soit créée (depuis la feuille MODELE que je viens de créer et qui est masquée) dès qu'il est ajouté dans la liste.
Jeudi je verrai la responsable et je vais finaliser avec elle la structure du fichier.
Bonne journée
Aloha

Re..
En ce qui concerne le nom voir dans:
menu Formule==>Gestionnaire de noms
Bonne idée pour la feuille modèle, il faudra revoir le fonctionnement
A mon avis, pour faire correctement, il faudrait une page de menu genre "Nouvel agent", "Modification", "Suppression", etc...
Et une constitution de base de donnée correctement organisée.
Mais bon, c'est une autre histoire......
Pour le reste attendons le feedback

 

[Aloha]

j'avais oublié de joindre le fichier avec la feuille MODELE et la correction des formules dans les colonnes I et J; j'y ai ajouté la fonction ARRONDI() pour limiter les chiffres après la virgule à 2.
Je viens de modifier à l'instant mon dernier message pour l'ajouter.

Seulement, je n'ai pas encore le feedback de la responsable et il vaut peut-être mieux ne pas trop continuer pour l'instant pour ne pas faire du travail inutilement.

Ce qui pourra déjà se faire en attendant, c'est l'ajoute de feuilles à partir de MODELE.

 

[Aloha]

Re,

A mon avis, pour faire correctement, il faudrait une page de menu genre "Nouvel agent", "Modification", "Suppression", etc...
Et une constitution de base de donnée correctement organisée.

Je ne comprends pas trop comment tu vois ça. Tu veux dire une feuille supplémentaire contenant des boutons? Nouvel agent, c'est clair. Mais Modification et suppression de quoi? d'un agent ou de données?

Je viens de téléphoner avec la responsable et j'ai procédé aux (menues) modifications qui s'en suivent:
*apparemment la colonne code n'est pas nécessaire. Cependant, comme je doute un peu s'il elle ne pourrait encore se révéler utile, je l'ai masquée plutôt que de la supprimer.
* la numérotation se fait manuellement, et j'ai enlevé les formules.
Pour le reste le fichier est ok.

Seulement, je me rends compte qu'il y a un problème dans TOUT. J'ai alors effacé toutes les données des fiches individuelles. Lorsque, à ce stade, je sélectionne TOUT, alors les noms des entêtes s'inscrivent autant de fois qu'il y a d'agents. Lorsque je saisis une opération, elle est inscrite entre deux lignes contenant les noms d'entêtes; lorsque je saisi une deuxième opération, elle n'est pas transférée dans TOUT.
Aucune idée de l'origine de ce problème. Je ne vois pas comment je pourrais en être moi le coupable avec mes modifications.

Bien à toi
Aloha

 

[Jacky67]

Re,
* la numérotation se fait manuellement, et j'ai enlevé les formules.
Pour le reste le fichier est ok.

Seulement, je me rends compte qu'il y a un problème dans TOUT. J'ai alors effacé toutes les données des fiches individuelles. Lorsque, à ce stade, je sélectionne TOUT, alors les noms des entêtes s'inscrivent autant de fois qu'il y a d'agents. Lorsque je saisis une opération, elle est inscrite entre deux lignes contenant les noms d'entêtes; lorsque je saisi une deuxième opération, elle n'est pas transférée dans TOUT.
Aucune idée de l'origine de ce problème. Je ne vois pas comment je pourrais en être moi le coupable avec mes modifications.

Bien à toi
Aloha

Re..
Aucune idée de l'origine de ce problème. Je ne vois pas comment je pourrais en être moi le coupable avec mes modifications.
Ben si.....🙄
La hauteur de la plage à copier dans les feuilles des agents était calculer sur la colonne U,
et comme les n° et la formule ont été supprimés.......plus de plage correcte.😵
Classeur modifié en Pj avec quelques améliorations de vitesse d'exécution.

 

[Aloha]

Bonjour
Comme ça on peut devenir coupable sans s'en douter! 😉
Le fichier fonctionne. Merci pour tout ce travail!
Ce qui n'est pas (encore) intégré c'est la création, à partir de MODELE, d'une nouvelle feuille, à l'ajoute d'un nouveau nom dans la liste, mais ce n'est pas obligé; ça peut aussi se faire manuellement.
J'ai essayé de remplir cette tâche en nregistrant une macro à attacher à un bouton à placer à côté de l'aire des listes à la hauteur du nouveau nom (évidemment à multiplier autant de fois qu'il y a de lignes vides), mais je me heurte au nom à coller dans l'onglet, puisque la macro colle le nom précis qui vient d'être saisi et non pas n'importe quel nom qui se trouve dans la cellule. Donc, même si je prévois autant de macros qu'il y a de lignes, ça ne peut pas marcher. Malheureusement mes connaissances en VBA ne vont pas assez loin pour remédier à cela et pour rendre la macro générique.
Pour le reste, cela semble fonctionner et je vais essayer d'appliquer ta solution à mon fichier réel.
Bonne journée
Aloha

 

[Jacky67]

Bonjour
Comme ça on peut devenir coupable sans s'en douter! 😉
Le fichier fonctionne. Merci pour tout ce travail!
Ce qui n'est pas (encore) intégré c'est la création, à partir de MODELE, d'une nouvelle feuille, à l'ajoute d'un nouveau nom dans la liste, mais ce n'est pas obligé; ça peut aussi se faire manuellement.
J'ai essayé de remplir cette tâche en nregistrant une macro à attacher à un bouton à placer à côté de l'aire des listes à la hauteur du nouveau nom (évidemment à multiplier autant de fois qu'il y a de lignes vides), mais je me heurte au nom à coller dans l'onglet, puisque la macro colle le nom précis qui vient d'être saisi et non pas n'importe quel nom qui se trouve dans la cellule. Donc, même si je prévois autant de macros qu'il y a de lignes, ça ne peut pas marcher. Malheureusement mes connaissances en VBA ne vont pas assez loin pour remédier à cela et pour rendre la macro générique.
Pour le reste, cela semble fonctionner et je vais essayer d'appliquer ta solution à mon fichier réel.
Bonne journée
Aloha

Re...
Aller... puisque c'est bientôt Noel
Une version un peu plus complète

 

[Aloha]

Bonsoir,

Merci pour le formidable cadeau de Noël!

Normalement, lorsque le cadeau présente une menue imperfection, on passe outre, par politesse.

J'ose être impoli et mentionner la très menue imperfection 😉:

Le bouton supprimant une feuille supprime uniquement la feuille, mais pas l'inscription dans la liste. Je ne le mentionnerais pas si le titre de l' Userform n'annonçait pas la suppression des deux. Mais c'est vraiment sans importance.

Entretemps (j'avais omis de t'en avertir) j'ai travaillé sur la cosmétique, j'ai caché le tableau à droite du tableau principal dans les feuilles individuelles, et j'ai ajouté un bouton pour imprimer le tableau caché.

Comme je n'ai pas réussi à intégrer tes modifications et ajoutes dans mon fichier, j'ai fait le contraire. J'avais effacé la liste des agents fictifs et je voulais ajouter les vrais un par un moyennant le nouveau bouton, mais la macro a beugué. J'ai alors désactivé les macros pour la cause, et j'ai fait un copier/coller depuis ma liste réelle, puis j'ai réactivé les macros.

Comme l'aire contenant les agents était beaucoup trop longue je lai raccourcie (après avoir déprotégé la feuille) jusqu'à la ligne 26. Seulement, lorsque j'ai ajouté, pour essayer, un nouveau nom, il s'est mis dans la 4ème ligne en-dessous du dernier nom. Ce qui n'est pas un problème non plus. J'ai trié la liste, et c'était bon.
Cependant, je crains que ce ne soit le raccourcissement de l'aire pouvant contenir les agents qui est encore fautif de cette "imperfection", donc moi-même une fois de plus!

Donc, je pense que ça peut fonctionner comme ça. Demain je verrai la responsable et je vais lui expliquer le fonctionnement.

La seule ajoute possible qui serait utile qui me vient à l'esprit, si tu en as encore envie, sinon ce n'est pas grave:

Ce serait bien si, dans AVANCES il y avait un tableau montrant les soldes des agents: solde initial, avances reçues, dépenses et solde final.

Comme ça la personne qui distribue les avances verrait d'un coup d'oeil, sans avoir à consulter les fiches individuelles, qui a besoin d'argent.

A toutes fins utiles j'ai intégré un tel tableau; j'ai mis provisoirement des formules pointant sur les feuilles présentes, mais cela doit bien évidemment se faire par macro, pour afficher les données de toutes les feuilles individuelles existantes, indépendamment du nom des onglets.

Normalement les formules avec INDIRECT() de la colonne J dans AVANCES devraient fonctionner, mais elles renvoient #REF.

Bonne soirée

Aloha

 

[Jacky67]

Re...

Vérifier le (bon) fonctionnement de cette version.
J'ai modifié la formule de la feuille Liste colonne H
Le classeur à ce code dans le Thisworkbook

Private Sub Workbook_Open()
    Sheets("Listes").Protect , UserinterfaceOnly:=True
End Sub

Il protège la feuille sans mot de passe et permet l'exécution du code vba sans se soucier de la protection
Les noms doivent donc être saisie ou supprimer par les boutons présents sur cette feuille
Les feuilles et les noms(création et suppression) devraient maintenant suivre. Je pense 😉
Comme demandé,
-la feuille "TOUT" est trier par date en décroissant,
-les totaux sont recalculés en fin de tableau par vba
-un bouton imprimer a été placer sur cette feuille (le code est à modifier selon que l'on imprimer directement ou pas)