Macro insertion et copie de ligne dans deux onglets à la fois

Le 01/10/2019 à 14:44

Bonjour,

Je reviens vers vous pour une autre demande.

Dans mon fichier, sur le premier onglet, je tiens un tableau de passage de formation, chaque ligne représente un salarié, et chaque colonne une information sur ce salarié.

Sur le deuxième onglet, je fais un simple report des informations du premier onglet via la formule =TEST!A1 etc etc que j'étend sur plusieurs lignes.

Sur le premier onglet, j'ai fais une macro pour insérer une ligne vierge partout où je veux dans mon tableau (je veux pouvoir insérer des nouvelles personnes où je veux dans le tableau, et pas les rajouter à la fin de celui-ci).

Cependant, si par exemple j'insère une ligne entre MARTIN et DUPONT, je voudrais que dans le deuxième onglet ce soit prit en compte, c'est à dire que cette nouvelle ligne soit aussi créée et surtout que la ligne y soit recopiée avec les formules =SI que j'ai mis dans les colonnes qui suivent. Car à l'état actuel, dans le deuxième onglet, l'ajout de ligne est ignorée et dans mes cellules les formules passent de =TEST!A2 à =TEST!A4, la nouvelle ligne qui est en A3 est ignorée.

Je voudrais qu'en insérant une ligne dans le premier onglet avec la macro, une ligne soit créée au même endroit dans le deuxième onglet (attention même endroit c'est à dire entre les mêmes deux noms de personne, pas sur la même ligne car TEST!A1=/=Feuil1!A1. Cependant si les cellules doivent être au même endroit je peux modifier je pense) tout en recopiant les formules =SI des colonnes du deuxième onglet .

Merci beaucoup de votre aide, en vous remerciant !

15fichier-test-macro.xlsx (11.55 Ko)
Le 07/10/2019 à 08:29

Bonjour Kaaes le forum

Super ton fichier exemple!!!!!

Tu parles de vouloir faire une insertion de ligne, dans la feuil 2 qui corresponde à la feuil1

seul petit problème, il n'y a qu'une seule feuille!!! et les formules elles c'est pareil elles sont restées coincées ou ???

bonne journée

Papou

Le 07/10/2019 à 08:42

Bonjour Kaaes , paritec

Je ne vois pas du tout à quoi cela sert de créer un autre onglet équivalent au premier.

Kaaes a écrit :

Sur le deuxième onglet, je fais un simple report des informations du premier onglet via la formule =TEST!A1 etc etc que j'étend sur plusieurs lignes.

Car à l'état actuel, dans le deuxième onglet, l'ajout de ligne est ignorée et dans mes cellules les formules passent de =TEST!A2 à =TEST!A4, la nouvelle ligne qui est en A3 est ignorée.

Une réponse (si je prends ta demande au pied de la lettre) est :

=decaler(TEST!$A$1;ligne()-1;colonne()-1)

J'attends la suite car j'entrevois le problème que tu vas rencontrer ! J'attends de voir quel est ton objectif qui je suppose est d'ajouter des informations dans le second onglet ! ?

Le 07/10/2019 à 10:07

Bonjour,

En fait le fichier sur lequel j'applique les macro est beaucoup plus complet que le fichier que je partage ici, mais je vais vous l'expliquer en floutant les parties confidentielles.

Je me sert d'un onglet comme base de données (Tableau de suivi) où j'écris les intitulés des formations et leurs dates d'expiration et l'autre onglet (ALERTES) vient mettre la valeur 1 pour chaque formation expirée ou arrivant à expiration, et je m'en sert pour créer une alerte email automatique en macro en venant chercher partout où il y a des 1.

Là où je rencontre mon problème, c'est sur l'onglet Planif Budget, explications :

Ici, je fais juste un report pour les colonnes A, B, C D et de M à T des données du premier onglet. Dans les colonnes E à L je fais cette formule :

=SI(ESTVIDE('Tableau de suivi'!H8);"0";SI(SI('Tableau de suivi'!H8-90<AUJOURDHUI();VRAI;FAUX)=VRAI;1;0)) afin de mettre la valeur 1 quand une formation arrive à expiration. Une macro vient ensuite analyser cet onglet et envoi un mail en disant que telle personne doit repasser telle formation. Ici, pas de soucis, j'ai juste fait cette macro là finalement pour mettre à jour le report, tout simplement :

Selection.AutoFill Destination:=Range("A2:U95"), Type:=xlFillDefault

Car c'est juste un report de données donc je peux me permettre de faire glisser les formules.

Ici est mon soucis, j'ai un troisième onglet pour suivre le budget, seulement ici je ne fais des reports que sur la moitié de la feuille (colonnes A à D).

Dans les colonnes E à K, je vais venir y inscrire au fur et à mesure, manuellement, les formations passées et le coût de la formation.

J'ai fait un report et une macro autofill pour les colonnes A à D, y'a pas de soucis, le problème c'est que par exemple, si j'ajoute un salarié entre la ligne 8 et 9, les informations dans les colonnes E à K ne seront plus en face du bon salarié car la liste se sera décalée. Je voudrais qu'en fonction de là où j'insère un salarié, ça décale les informations présentes dans les colonnes E à K d'une ligne.

Si je reprend mon exemple, si j'insère un salarié entre la ligne 8 et 9, Le salarié en ligne 9 va passer ligne 10, mais les informations rattachées à ce salarié resteront en ligne 9, devant le nouveau salarié intégré. Je souhaiterais donc décaler les informations dans les colonnes E à K en ligne 9 automatiquement à la ligne 10 (en fait décaler tout le tableau d'une ligne vers le bas juste dans les colonnes E à K).

Voilà j'espère avoir été clair, c'est un peu l'usine à gaz désolé !

Merci !

Le 07/10/2019 à 11:12
Kaaes a écrit :

Ici, je fais juste un report pour les colonnes A, B, C D et de M à T des données du premier onglet. Dans les colonnes E à L je fais cette formule :

=SI(ESTVIDE('Tableau de suivi'!H8);"0";SI(SI('Tableau de suivi'!H8-90<AUJOURDHUI();VRAI;FAUX)=VRAI;1;0))

Si cet onglet est uniquement lié à ton onglet BdD base de données , tu peux continuer avec des formules DECALER

=SI(ESTVIDE(DECALER('Tableau de suivi'!$H$8;LIGNE()-1;));"0";SI(SI(DECALER('Tableau de suivi'!$H$8;LIGNE()-1;)-90<AUJOURDHUI();VRAI;FAUX)=VRAI;1;0))
Kaaes a écrit :

Ici est mon soucis, j'ai un troisième onglet pour suivre le budget, seulement ici je ne fais des reports que sur la moitié de la feuille (colonnes A à D).

Dans les colonnes E à K, je vais venir y inscrire au fur et à mesure, manuellement, les formations passées et le coût de la formation.

Là, cela ne va plus du tout ! Si tu ajoutes des informations manuelles, une insertion de ligne dans ta BdD base de données , avec DECALER, tes premières infos A à D seront bien décalées mais pas les colonnes E à K !! plus rien ne va correspondre.

2 solutions :

  • tu mets les infos des colonnes E à K sur le premier onglet.
  • ou alors tu mets un identifiant (matricule) sur chacun qui fera le lien entre les informations avec une fonction RECHERCHEV.
Le 07/10/2019 à 11:21

Tu te retrouves dans la même situation qu'ici : https://forum.excel-pratique.com/viewtopic.php?p=705517#p705517

Le 07/10/2019 à 15:07

Oui en fait j'ai mis des colonnes à la suite dans mon onglet "Tableau de suivi". Je vais faire comme ça du coup ça marche et c'est pas trop bordélique.

Cependant petite question sur la fonction AutoFill, je suis obligé de déterminer jusqu'à quelle ligne je veux faire l'AutoFill, mais si je met le nombre exact de ligne de mon tableau, quand je rajoute un salarié, la dernière ligne n'est pas comptabilisée dans l'autofill car du coup entre temps le tableau a grandi d'une ligne.Si je met un nombre de ligne supérieure pour avoir de la marge, ça me glisse la formule plus bas et ça "déborde", c'est moche.

il n'existe pas une fonction pour faire un AutoFill juste sur les lignes où il y a un nom de salarié ?

J'ai essayé ça mais ça me fait une erreur :

lig_depart = 8

lig = lig_depart - 1

Do

    lig = lig + 1

    Columns("Z:Z").Select
    Selection.AutoFill Destination:=Columns("Z:Z"), Type:=xlFillDefault
    Columns("Z:Z").Select
    Columns("D:D").Select
    Selection.AutoFill Destination:=Columns("Z:Z"), Type:=xlFillDefault
    Columns("D:D").Select

Loop Until Sheets("Tableau de suivi").Cells(lig, A) = ""
Le 07/10/2019 à 18:33

Je n'ai pas compris ta demande et ton code. peux-tu le mettre dans n fichier simplifié ?

Si tes données sont structurées en tableau, tu n'as pas à utiliser autofill normalement !

Le 08/10/2019 à 09:15

Bonjour,

Voici mon fichier test.

J'ai fais une formule dans la colonne J en fonction de la colonne C (si C = 1 alors J = A planifier).

Quand j'ajoute une ligne entre DUPONT et MARTIN, ok pas de soucis car ma macro va faire le AutoFill de J2 à J5.

Quand je rajoute une autre ligne au même endroit, entre DUPONT et MARTIN, pas de soucis non plus car ça met à jour le numéro de ligne en J6 vu qu'elle se fait décaler et qu'il y a déjà une formule dedans.

Cependant, si maintenant je veux rajouter une ligne entre DUCHAMP et MARTIN, le AutoFill n'allant que de J2 à J5.

Je voudrais que la macro comprenne qu'il faut faire le Autofill jusqu'à la fin du tableau, peu importe où j'insère ma ligne.

Lire dire que temps que la macro ne rencontre pas de cellule vide dans la colonne A, il continue à faire le AutoFill en colonne J.

(Pour précision, sur mon fichier quand j'insère une ligne, j'ai un userform qui vient remplir celle-ci sur les colonnes A & B).

Mon lig_depart c'est parce que je veux qu'il commence à analyser la colonne A à partir de la ligne 8 dans mon vrai tableau.

13fichier-test-macro.xlsm (18.32 Ko)
Le 08/10/2019 à 09:43

Passe tes données en tableau et tu n'auras pas à faire d'autofill.

14fichier-test-macro.xlsm (17.67 Ko)
Le 08/10/2019 à 09:47

Bonjour Kaees steelson le forum

pour faire ce que tu veux voilà

a+

Papou 

Sub insertionLigne()
    Dim iRow&, fin&
    iRow = Selection.Row
    Rows(iRow).Insert Shift:=xlDown
    fin = Cells.Find("*", , xlValues, , 1, 2, 0).Row
    Range("J2").AutoFill Range(Cells(2, 10), Cells(fin, 10))
End Sub
Le 08/10/2019 à 10:14

Okay je l'ai passé en tableau merci.

Cependant est-il possible dans la formule [@[COLONNE]] d'aller chercher une colonne dans un autre tableau ? car ma base de données et mes alertes sont dans deux onglets différents, et je me sert des alertes avec les 1 et 0 pour ma formule =SI(ALERTES!E4=1;"A planifier";"Aucun besoin").

Le 08/10/2019 à 10:19
paritec a écrit :

Bonjour Kaees steelson le forum

pour faire ce que tu veux voilà

a+

Papou 

Sub insertionLigne()
    Dim iRow&, fin&
    iRow = Selection.Row
    Rows(iRow).Insert Shift:=xlDown
    fin = Cells.Find("*", , xlValues, , 1, 2, 0).Row
    Range("J2").AutoFill Range(Cells(2, 10), Cells(fin, 10))
End Sub

Salut !

j'ai déjà ça dans ma macro d'insertion ligne, je dois faire le tri dans ta commande ?

Sub insertionLigne()
    Dim iRow As Integer

     'note the current row
    iRow = Selection.Row
     'Insert a new row
    Selection.EntireRow.Select
    Selection.Insert Shift:=xlDown
     'Select the original row and copy it with formulae
    Rows(iRow + 1).Copy

     'Paste it into the new row
    Rows(iRow).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("ALERTES").Select
    Range("A2:U2").Select
    Selection.AutoFill Destination:=Range("A2:U95"), Type:=xlFillDefault
    Range("A2:U95").Select
    Sheets("Tableau de suivi").Select

End Sub
Le 08/10/2019 à 11:26

Ok c'est bon résolu !!

Merci énormément à vous tous !

Le 08/10/2019 à 11:39

Re Kaees le forum

bah oui il suffit de remplacer ta macro, par celle que je t'ai envoyé

a+

Papou

Le 08/10/2019 à 16:43

Non car j'ai besoin de ma macro aussi pour d'autres choses haha

j'ai intégré la macro "fin" et ça a suffit !

Le 09/10/2019 à 09:09

Re Kaees le forum

La macro fin ?? tu es trop fort pour moi

fin sans la ligne du dessous ne sert à rien!!

a+

Papou

Rechercher des sujets similaires à "macro insertion copie ligne deux onglets fois"