Microsoft 365 Tableau "fils" basé sur une colonne

[Gégé-45550]

Bonjour amis EXCELlents et meilleurs vœux pour cette fin d'année.
J'ai sur une feuille (un onglet) un tableau de données.
Par exemple :

A	alpha	toto
B	beta	tata
C	delta	lolo
D	gamma	lili
E	epsilon	lulu
F	rho	pomme
G	phi	poire
H	pi	prune

Je voudrais créer sur une autre feuille (un autre onglet) un autre tableau de données dont la première colonne serait un clone dynamique de la première colonne du tableau précédemment cité (ici, la colonne qui contient les données A,B,C, D etc.
En d'autres termes, j'aimerais que, si je change par exemple la valeur "C" de la troisième ligne de la première colonne du premier tableau, par exemple en "Ç", la valeur de la troisième ligne de la première colonne du deuxième tableau soit elle aussi automatiquement changée en "Ç". De même, il faudrait que, si je supprime ou si j'ajoute une ou plusieurs lignes au tableau de la première feuille, une ou plusieurs lignes soient automatiquement ajoutées au tableau de la deuxième feuille et, bien sûr, que les données de la première colonne soient recopiées.
J'ai une solution sans macros, qui consiste à remplir la première colonne de la deuxième feuille avec des formules du type : =INDIRECT("Feuil1!"&ADRESSE(LIGNE(A1);COLONNE(A1))).
Cette solution très simple fonctionne parfaitement sauf ... que j'ai des données sous le tableau en Feuille2 et que celle-ci ne sont bien sûr pas décalées avec cette méthode et se retrouvent donc "écrasées" dès que j'insère des lignes.
Je joins un petit fichier de test pour illustrer mon problème.

Si quelqu'un pouvait m'aider, je lui en serais très reconnaissant.
Merci d'avance.

 

[mapomme]

Bonjour @Gégé-45550,

Un essai via une macro évènementielle dans le module de la feuille "Feuil2".
Cette macro se déclenche quand on sélectionne la feuille "Feuil2".

Votre question soulève un problème de taille. Comment, sur la feuille "Feuil2", peut-on distinguer la ligne à décaler alors que rien ne sépare le tableau de cette ligne ?

Pour pallier cette difficulté, la première fois qu'on sélectionne la feuille "Feuil2", la macro va demander à l'utilisateur de désigner la première ligne à décaler (dans l'exemple que vous avez fourni c'est la ligne 9.
Une fois cette ligne désignée, la macro ne le demandera plus jusqu'à une nouvelle ouverture du classeur.

Nota : pour définir le tableau à copier de la feuille "Feuil1", on utilise la colonne A.

Le code du module de la feuille "Feuil2":

Sub Worksheet_Activate()
Dim xrg As Range, premlig As Range, derlig, source As Range
Static PremLigOK As Boolean

'pour que cela fonctionne, il faut dans un premier temps définir
'quelle est la première ligne sous le tableau à décaler
'on va donc demander à l'utilisateur de la désigner
'une fois cela fait, la macro ne le demandera plus
'jusqu'à ce que le classeur soit refermé.
If Not PremLigOK Then
   Application.ScreenUpdating = True
   Do
      On Error Resume Next: Set xrg = Nothing
      Set xrg = Application.InputBox( _
            "Sélectionnez une cellule quelconque de la première ligne sous le tableau à décaler...", _
            Type:=8)
   Loop Until Not xrg Is Nothing
   On Error GoTo 0
   ActiveWorkbook.Names("PremLig").Delete
   ActiveWorkbook.Names.Add Name:="PremLig", RefersToR1C1:=xrg.EntireRow.Rows(1)
   PremLigOK = True
   Application.ScreenUpdating = False
End If

'on efface les lignes entières correspondant au tableau précédent de Feuil2
'pour y coller le tableau de la Feuil1
With Sheets("feuil2")
   Set premlig = Range("premlig")
   If premlig.Row > 1 Then .Rows(1).Resize(premlig.Row - 1).Delete
   With Sheets("feuil1")
      derlig = .Cells(.Rows.Count, "a").End(xlUp).Row
      If derlig = 1 And .Range("a1") = "" And .Range("b1") = "" And .Range("c1") = "" Then Exit Sub
      Set source = .Range("a1:c" & derlig)
   End With
   .Range("a1").Resize(source.Rows.Count).EntireRow.Insert
   source.Copy .Rows(1)
End With
End Sub

 

[Gégé-45550]

Bonjour @Gégé-45550,

Un essai via une macro évènementielle dans le module de la feuille "Feuil2".
Cette macro se déclenche quand on sélectionne la feuille "Feuil2".

Votre question soulève un problème de taille. Comment, sur la feuille "Feuil2", peut-on distinguer la ligne à décaler alors que rien ne sépare le tableau de cette ligne ?

Pour pallier cette difficulté, la première fois qu'on sélectionne la feuille "Feuil2", la macro va demander à l'utilisateur de désigner la première ligne à décaler (dans l'exemple que vous avez fourni c'est la ligne 9.
Une fois cette ligne désignée, la macro ne le demandera plus jusqu'à une nouvelle ouverture du classeur.

Nota : pour définir le tableau à copier de la feuille "Feuil1", on utilise la colonne A.

Le code du module de la feuille "Feuil2":

Sub Worksheet_Activate()
Dim xrg As Range, premlig As Range, derlig, source As Range
Static PremLigOK As Boolean

'pour que cela fonctionne, il faut dans un premier temps définir
'quelle est la première ligne sous le tableau à décaler
'on va donc demander à l'utilisateur de la désigner
'une fois cela fait, la macro ne le demandera plus
'jusqu'à ce que le classeur soit refermé.
If Not PremLigOK Then
   Application.ScreenUpdating = True
   Do
      On Error Resume Next: Set xrg = Nothing
      Set xrg = Application.InputBox( _
            "Sélectionnez une cellule quelconque de la première ligne sous le tableau à décaler...", _
            Type:=8)
   Loop Until Not xrg Is Nothing
   On Error GoTo 0
   ActiveWorkbook.Names("PremLig").Delete
   ActiveWorkbook.Names.Add Name:="PremLig", RefersToR1C1:=xrg.EntireRow.Rows(1)
   PremLigOK = True
   Application.ScreenUpdating = False
End If

'on efface les lignes entières correspondant au tableau précédent de Feuil2
'pour y coller le tableau de la Feuil1
With Sheets("feuil2")
   Set premlig = Range("premlig")
   If premlig.Row > 1 Then .Rows(1).Resize(premlig.Row - 1).Delete
   With Sheets("feuil1")
      derlig = .Cells(.Rows.Count, "a").End(xlUp).Row
      If derlig = 1 And .Range("a1") = "" And .Range("b1") = "" And .Range("c1") = "" Then Exit Sub
      Set source = .Range("a1:c" & derlig)
   End With
   .Range("a1").Resize(source.Rows.Count).EntireRow.Insert
   source.Copy .Rows(1)
End With
End Sub

Bonjour @Gégé-45550,

Un essai via une macro évènementielle dans le module de la feuille "Feuil2".
Cette macro se déclenche quand on sélectionne la feuille "Feuil2".

Votre question soulève un problème de taille. Comment, sur la feuille "Feuil2", peut-on distinguer la ligne à décaler alors que rien ne sépare le tableau de cette ligne ?

Pour pallier cette difficulté, la première fois qu'on sélectionne la feuille "Feuil2", la macro va demander à l'utilisateur de désigner la première ligne à décaler (dans l'exemple que vous avez fourni c'est la ligne 9.
Une fois cette ligne désignée, la macro ne le demandera plus jusqu'à une nouvelle ouverture du classeur.

Nota : pour définir le tableau à copier de la feuille "Feuil1", on utilise la colonne A.

Le code du module de la feuille "Feuil2":

Sub Worksheet_Activate()
Dim xrg As Range, premlig As Range, derlig, source As Range
Static PremLigOK As Boolean

'pour que cela fonctionne, il faut dans un premier temps définir
'quelle est la première ligne sous le tableau à décaler
'on va donc demander à l'utilisateur de la désigner
'une fois cela fait, la macro ne le demandera plus
'jusqu'à ce que le classeur soit refermé.
If Not PremLigOK Then
   Application.ScreenUpdating = True
   Do
      On Error Resume Next: Set xrg = Nothing
      Set xrg = Application.InputBox( _
            "Sélectionnez une cellule quelconque de la première ligne sous le tableau à décaler...", _
            Type:=8)
   Loop Until Not xrg Is Nothing
   On Error GoTo 0
   ActiveWorkbook.Names("PremLig").Delete
   ActiveWorkbook.Names.Add Name:="PremLig", RefersToR1C1:=xrg.EntireRow.Rows(1)
   PremLigOK = True
   Application.ScreenUpdating = False
End If

'on efface les lignes entières correspondant au tableau précédent de Feuil2
'pour y coller le tableau de la Feuil1
With Sheets("feuil2")
   Set premlig = Range("premlig")
   If premlig.Row > 1 Then .Rows(1).Resize(premlig.Row - 1).Delete
   With Sheets("feuil1")
      derlig = .Cells(.Rows.Count, "a").End(xlUp).Row
      If derlig = 1 And .Range("a1") = "" And .Range("b1") = "" And .Range("c1") = "" Then Exit Sub
      Set source = .Range("a1:c" & derlig)
   End With
   .Range("a1").Resize(source.Rows.Count).EntireRow.Insert
   source.Copy .Rows(1)
End With
End Sub

Bonjour mapomme,
Bravo ! ... et merci !
De la très belle ouvrage ! Meilleurs vœux de fin d'année.

 

[CHRIS1945]

Bonjour,
Je me permet de proposer une alternative car j'ai interprété la demande de manière différente.
Comme le disais très justement mapomme, la demande était complexe et il m'a fallu un certain temps pour mettre au point une réponse valable
J'ai en effet compris que l'adaptation du Tableau 2 ne devait se faire que pour la première colonne et que l'on devait conserver les informations du tableau 2.

Voici donc en annexe une autre proposition.

Il y a un onglet explicatif dans le classeur et le VBA est documenté afin de bien suivre son évolution.

S'il y a des problèmes n'hésite pas à me revenir
Chris

 

[Gégé-45550]

Bonjour,
Je me permet de proposer une alternative car j'ai interprété la demande de manière différente.
Comme le disais très justement mapomme, la demande était complexe et il m'a fallu un certain temps pour mettre au point une réponse valable
J'ai en effet compris que l'adaptation du Tableau 2 ne devait se faire que pour la première colonne et que l'on devait conserver les informations du tableau 2.

Voici donc en annexe une autre proposition.

Il y a un onglet explicatif dans le classeur et le VBA est documenté afin de bien suivre son évolution.

S'il y a des problèmes n'hésite pas à me revenir
Chris

Génial !!!
C'est exactement la réponse à la question posée, dont je n'osais rêver.
J'étais en train d'essayer d'adapter la solution proposée par "mapomme" dans ce sens et voilà que, grâce à vous, l'année se termine en beauté.
Il me reste juste à ajouter éventuellement un flag qui marquerait tout changement dans la première colonne du tableau1 de la Feuil1 afin d'éviter la procédure si rien n'a changé. Je devrais pouvoir y arriver.
Petite question subsidiaire : Ne pourrait-on pas se contenter de recopier une ligne de formules dans le Tableau3 de la Feuil3 (à partir de la deuxième colonne) puis de recopier ensuite ces formules dans chaque ligne recréée dans le Tableau2 de la Feuil2 (toujours à partir de la deuxième colonne) ?

Merci encore et excellente soirée de réveillon !

 

[CHRIS1945]

Bonjour,

Content que cela te convienne.
Pour répondre à tes questions,
1 il ne faut pas intégrer un flag pour savoir s'il faut ou pas pour le changement puisque le VBA se déclenche automatiquement dès que tu actives l'onglet Feuil2. Si il n'y a rien de changer dans la Feuil1... cela n'a pas d'importance : le VBA tourne en 1/4 de seconde et te remet exactement les mêmes données avec en plus que
- le Tableau3 sera MAJ à la dernière mouture du Tableau2 (sinon il est toujours avec la version précédente)
- le Tableau2 entérinera les valeurs que tu auras insérées dans le Tableau2 en les remplaçant par la formule de RECHERCHE.

2 la procédure que tu suggères pour la mise à jour du Tableau3 compliquerait à l’extrême le VBA.

N'oublies pas que ta demande implique que l'on puisse non seulement ajouter des lignes mais aussi en intercaler et en supprimer.

Pour effectuer ce que tu suggères, il faudrait tester toutes les lignes du Tableau2 pour voir si elles existent ou pas dans le Tableau3 et alors adapter le Tableau3 en supprimant ou insérant ou ajoutant des lignes... puis ensuite refaire la même chose pour le Tableau2...de quoi devenir dingue.
Il est beaucoup plus simple de supprimer totalement et le reconstruire à chaque fois.

Les instructions qui imposent les 2 formules de RECHERCHEV ne sont utiles que si tu modifies les informations de la première ligne du Tableau2.

En effet, il faut savoir qu'Excel adapte automatiquement les cellules d'un Tableau aux formules se trouvant dans la première ligne MAIS n'adapte pas la première ligne... il faut donc palier à ce manquement car si tu changeais les données dans la première ligne du Tableau2 pour les colonnes B et C... cela resterait des valeurs absolues et les formules ne reviendraient pas.

Ton application à de multiple possibilités et j'ai essayé d'y répondre le plus simplement possible. Le VBA est relativement succin et crois moi j'y ai passé quelques heures pour en arriver là.
J'avais déjà posté un premier essai il y a quelques jours et je l'ai supprimé en me rendant compte que j'avais oublié un cas de figure.

Si tu as des difficultés à mettre en place dans ton classeur définitif n'hésite pas à me revenir.

Il me reste à te souhaiter une bonne et heureuse année 2020 pleine de développements Excel.
Chris

 

[Gégé-45550]

Bonjour,

Content que cela te convienne.
Pour répondre à tes questions,
1 il ne faut pas intégrer un flag pour savoir s'il faut ou pas pour le changement puisque le VBA se déclenche automatiquement dès que tu actives l'onglet Feuil2. Si il n'y a rien de changer dans la Feuil1... cela n'a pas d'importance : le VBA tourne en 1/4 de seconde et te remet exactement les mêmes données avec en plus que
- le Tableau3 sera MAJ à la dernière mouture du Tableau2 (sinon il est toujours avec la version précédente)
- le Tableau2 entérinera les valeurs que tu auras insérées dans le Tableau2 en les remplaçant par la formule de RECHERCHE.

2 la procédure que tu suggères pour la mise à jour du Tableau3 compliquerait à l’extrême le VBA.

N'oublies pas que ta demande implique que l'on puisse non seulement ajouter des lignes mais aussi en intercaler et en supprimer.

Pour effectuer ce que tu suggères, il faudrait tester toutes les lignes du Tableau2 pour voir si elles existent ou pas dans le Tableau3 et alors adapter le Tableau3 en supprimant ou insérant ou ajoutant des lignes... puis ensuite refaire la même chose pour le Tableau2...de quoi devenir dingue.
Il est beaucoup plus simple de supprimer totalement et le reconstruire à chaque fois.

Les instructions qui imposent les 2 formules de RECHERCHEV ne sont utiles que si tu modifies les informations de la première ligne du Tableau2.

En effet, il faut savoir qu'Excel adapte automatiquement les cellules d'un Tableau aux formules se trouvant dans la première ligne MAIS n'adapte pas la première ligne... il faut donc palier à ce manquement car si tu changeais les données dans la première ligne du Tableau2 pour les colonnes B et C... cela resterait des valeurs absolues et les formules ne reviendraient pas.

Ton application à de multiple possibilités et j'ai essayé d'y répondre le plus simplement possible. Le VBA est relativement succin et crois moi j'y ai passé quelques heures pour en arriver là.
J'avais déjà posté un premier essai il y a quelques jours et je l'ai supprimé en me rendant compte que j'avais oublié un cas de figure.

Si tu as des difficultés à mettre en place dans ton classeur définitif n'hésite pas à me revenir.

Il me reste à te souhaiter une bonne et heureuse année 2020 pleine de développements Excel.
Chris

Entendu !
Merci beaucoup et bonne année !