Copie d'une ligne dans un autre onglet si une colonne est complété

[association]

Bonjour à tous,

Mon problème n'est pas très compliqué, mais c'est surtout que j'ai du mal à le formuler et donc savoir ou chercher pour trouver une réponse (en gros, je patauge).

Pour faire simple, j'ai un onglet 'BASE' et j'aimerai :
- que lorsque la colonne 'D' contient une information, la ligne ce copie dans l'onglet 1,
- que lorsque la colonne 'E' contient une information, la ligne ce copie dans l'onglet 2,
- .... quelque soit le nombre de colonnes

Je sais que c'est un truc tout simple mais j'aurai besoin d'un peu d'aide.
Ci-joint un fichier exemple.

Merci d'avance

 

[Modeste]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Bonsoir association,

Un problème simple, un problème simple, c'est à voir!

On peut y arriver par formules ou par macro.

Si on travaille avec des formules, il y a "l'artillerie lourde" que sont les formules matricielles; celles-ci sont gourmandes en ressources et risquent de ralentir les calculs si tu as de gros volumes à traiter.
Si on veut éviter les formules matricielles, on peut toujours imaginer une solution par formules (en "sacrifiant" éventuellement 2 colonnes de ta feuille "BASE", pour y stocker des infos)
Par macro, on peut faire en sorte que le contenu des feuilles "1" et "2" soit mis à jour chaque fois qu'on active une de ces deux feuilles.
Même si ton exemple n'est pas très "parlant", il est possible aussi qu'un Tableau Croisé Dynamique donne un résultat proche de ce que tu cherches.
Un simple filtre (suivi éventuellement d'un copier-coller) donnerait sans doute aussi les résultats voulus.

Il faudrait que tu précises le volume maximum que tu aurais à gérer. Quand tu dis "quelque soit le nombre de colonnes", on peut tout de même supposer que ce nombre ne va pas varier "en cours de route"? Tu vas utiliser ce même fichier pendant une longue période et à intervalles réguliers tu auras besoin de "récupérer" les infos de tes 2 feuilles "secondaires" ou alors tu complètes la "BASE" en une fois et tu utilises les infos des 2 autres feuilles une seule fois, avant de tout recommencer?

Bref, donne-nous des précisions supplémentaires. C'est en fonction de celles-ci qu'une solution la plus adaptée pourra être formulée.

 

[association]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Bonsoir Modeste,

Ah, mon problème n'est pas simple!!!!

Mais je vais déjà essayer d'expliquer tout ça simplement

En fait, je cherche un moyen pas trop compliqué et pas trop chronophage pour gérer les défraiements de mes bénévoles. Mon idée était de faire figurer dans l'onglet "BASE" : la liste des bénévoles (en abscisse) et la liste des déplacements (en ordonnée) et ensuite de compléter ce tableau avec les km réalisés par chaque bénévole (sachant qu'ils ne sont pas tous présent à chaque fois). Et de renvoyer ensuite ces informations pour chaque bénévole dans un récapitulatif annuel (un onglet pour chacun) que je n'aurai plus qu'a imprimer en fin d'année.
Voilà mon idée de base et ce qui me semblait, simple... Mais je suis preneur de tout conseil pour optimiser tout ça.

Je pense que j'utiliserai ce fichier de nombreuses années et j'aurai préféré une solution sans macro, seulement avec des formules (ça me semble plus facile à bidouiller par la suite). Sachant que j'imagine des variantes du fichier pour d'autres utilisations (mettre en abscisses les mois de l'année, planning,...). Mais je pense que c'est largement suffisant avec 20 colonnes et donc 20 onglets.

Je reste à ta disposition pour t'apporter d'autres précisions et voir avec toi (ou d'autres) les solutions à envisager pour réaliser simplement ce projet

Ps Je suis un utilisateur amateur d'Excel, qui arrive à bidouiller des petites formules mais qui pige rien aux macros.

 

[job75]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Bonsoir association, bonsoir cher Modeste,

Je suis un utilisateur amateur d'Excel (... ) mais qui pige rien aux macros.

Et bien tant pis, voyez quand même le fichier joint et cette macro événementielle dans le code de la feuille (clic droit sur l'onglet et Visualiser le code) :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lig&, t1, t2, t3, t4, t5, x$, P As Range, tablo, i&
Set Target = Intersect(Target, Me.UsedRange)
If Target Is Nothing Then Exit Sub
For Each Target In Target 'en cas d'entrées multiples
  lig = Target.Row
  If lig > 3 Then
    t1 = Cells(lig, 1): t2 = Cells(lig, 2)
    t3 = Cells(lig, 3): t4 = Cells(lig, 4): t5 = Cells(lig, 5)
    If t1 <> "" And t2 <> "" And t3 <> "" Then
      If t4 <> "" Then
        x = t1 & t2 & t3 & t4
        With Sheets("1")
          Set P = .Range("A3:D" & .Range("A" & .Rows.Count).End(xlUp).Row)
        End With
        tablo = P 'matrice, plus rapide
        For i = 1 To UBound(tablo)
          If x = tablo(i, 1) & tablo(i, 2) & tablo(i, 3) & tablo(i, 4) Then GoTo 1
        Next
        P(i, 1) = t1: P(i, 2) = t2: P(i, 3) = t3: P(i, 4) = t4
      End If
1     If t5 <> "" Then
        x = t1 & t2 & t3 & t5
        With Sheets("2")
          Set P = .Range("A3:D" & .Range("A" & .Rows.Count).End(xlUp).Row)
        End With
        tablo = P 'matrice, plus rapide
        For i = 1 To UBound(tablo)
          If x = tablo(i, 1) & tablo(i, 2) & tablo(i, 3) & tablo(i, 4) Then GoTo 2
        Next
        P(i, 1) = t1: P(i, 2) = t2: P(i, 3) = t3: P(i, 4) = t5
      End If
    End If
  End If
2 Next
End Sub

Bonne nuit et A+

 

[job75]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Re,

Il est bien sûr tout à fait souhaitable que les feuilles 1 et 2 soient triées par dates.

Il suffit de le faire quand on active ces feuilles :

Private Sub Worksheet_Activate()
Range("A3:E" & Range("A" & Rows.Count).End(xlUp).Row) _
  .Sort [A3], xlAscending, Header:=xlYes
End Sub

Fichier (2).

Encore bonne nuit.

 

[job75]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Re,

Je ne comprends pas, quand on valide 2 fois sur une même ligne cette ligne est copiée 2 fois.

Pourtant j'ai pris la précaution de faire une boucle de recherche pour éviter ça

Je verrai ce problème demain.

A+

 

[job75]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Re,

Ah oui j'avais écrit bêtement t1 = Cells(lig, 1).Value2 alors qu'il faut t1 = Cells(lig, 1)

Je corrige les 2 fichiers précédents.

A+

 

[job75]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Bonjour association, Modeste, le forum,

Plusieurs compléments importants dans ce fichier (3).

1) Les bordures des tableaux sont appliquées par Mise en forme conditionnelle (MFC).

2) Pour éviter des erreurs, les feuilles 1 et 2 sont protégées avec mot de passe par cette macro dans ThisWorkbook :

Private Sub Workbook_Open()
Sheets("1").Protect "TOTO", UserInterfaceOnly:=True 'mot de passe à adapter
Sheets("2").Protect "TOTO", UserInterfaceOnly:=True 'mot de passe à adapter
End Sub

Notez que les macros s'exécutent sans qu'il soit nécessaire d'ôter la protection.

Les cellules de la colonne DIVERS sont déverrouillées pour être renseignées manuellement.

3) Des lignes en feuille "BASE" peuvent être modifiées ou supprimées, il faut donc "nettoyer" les tableaux des feuilles 1 et 2 lors de leur activation :

Private Sub Worksheet_Activate()
Dim P As Range, tablo, ub&, i&, dat, x$, j&
Application.ScreenUpdating = False
With Sheets("BASE")
  On Error Resume Next: .ShowAllData: On Error GoTo 0
  Set P = .Rows("3:" & .Range("A" & Rows.Count).End(xlUp).Row)
End With
P.Sort P(1), xlAscending, Header:=xlYes 'tri sur les dates
tablo = P.Resize(, 5) 'matrice, plus rapide
ub = UBound(tablo)
Set P = Range("A3:E" & Range("A" & Rows.Count).End(xlUp).Row)
P.Sort P(1), xlAscending, Header:=xlYes 'tri sur les dates
For i = P.Rows.Count To 2 Step -1
  dat = P(i, 1): x = dat & P(i, 2) & P(i, 3) & P(i, 4)
  For j = ub To 2 Step -1
    If tablo(j, 1) < dat Then Exit For
    If x = tablo(j, 1) & tablo(j, 2) & tablo(j, 3) & tablo(j, 4) Then GoTo 1
  Next
  P.Rows(i).Delete xlUp 'suppression de la ligne non trouvée
1 Next
End Sub

A+

 

[Modeste]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Bonjour association,
Salut job, bonjour et belle journée (après tout, tu as dit trois fois "bonne nuit"). J'y ajoute un 'hello' pour répondre à tes salutations de ce matin!

Une solution avec des formules matricielles (ma remarque sur le temps de calcul reste valable, cela va sans dire)

J'ai laissé la seule feuille concernant "MONSIEUR 1". Il convient de tester et, éventuellement d'adapter les formules. Une même formule est utilisée dans les 3 premières colonnes (en bleu) et peut donc être recopiée vers le bas si nécessaire. Il faudra éventuellement adapter la plage BASE!A$1:A$100, ainsi que BASE!$D$3:$Z$3 dans ces formules.
Ne pas oublier de valider avec Ctrl+Shift+Enter, en cas de modification.

La formule de la colonne D est un poil différente (les mêmes adaptations devront y être apportées, le cas échéant)
La colonne "DIVERS" sera complétée manuellement, je suppose!?

Si ça fonctionne comme attendu, pour tout nouveau bénévole, faire une copie de la feuille "1" (clic droit sur l'onglet, cocher "créer une copie" et la placer "en dernier") renommer cette feuille et en cellule A1, indiquer le nom, précisément tel qu'il apparaît en feuille BASE dans la plage D3:Z3.

 

[job75]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Re,

Sois sûr que je vais regarder tes formules Modeste

Pour l'instant je continue le VBA, avec ce fichier (4) adapté à un nombre quelconque de feuilles.

Le code dans ThisWorkbook :

Private Sub Workbook_Open()
Dim w As Worksheet
For Each w In Worksheets
  If Application.CountIf(Sheets("BASE").Rows(3), w.Name) Then _
    w.Protect "TOTO", UserInterfaceOnly:=True 'mot de passe à adapter
Next
Me.Saved = True 'évite l'invite à la fermeture
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim col As Variant, P As Range, tablo, ub&, i&, dat, x$, j&
With Sheets("BASE")
  col = Application.Match(Sh.Name, .Rows(3), 0)
  If IsError(col) Then Exit Sub
  On Error Resume Next: .ShowAllData: On Error GoTo 0
  Set P = .Rows("3:" & .Range("A" & Rows.Count).End(xlUp).Row)
End With
Application.ScreenUpdating = False
P.Sort P(1), xlAscending, Header:=xlYes 'tri sur les dates
tablo = P.Resize(, col) 'matrice, plus rapide
ub = UBound(tablo)
Set P = Sh.Range("A3:E" & Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row)
P.Sort P(1), xlAscending, Header:=xlYes 'tri sur les dates
For i = P.Rows.Count To 2 Step -1
  dat = P(i, 1): x = dat & P(i, 2) & P(i, 3) & P(i, 4)
  For j = ub To 2 Step -1
    If tablo(j, 1) < dat Then Exit For
    If x = tablo(j, 1) & tablo(j, 2) & tablo(j, 3) & tablo(j, col) Then GoTo 1
  Next j
  P.Rows(i).Delete xlUp 'suppression de la ligne non trouvée
1 Next i
End Sub

Le code de la feuille "BASE" :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lig&, t1, t2, t3, w As Worksheet, col As Variant, t, x$, P As Range, tablo, i&
Set Target = Intersect(Target, Me.UsedRange)
If Target Is Nothing Then Exit Sub
For Each Target In Target 'en cas d'entrées multiples
  lig = Target.Row
  If lig > 3 Then
    t1 = Cells(lig, 1): t2 = Cells(lig, 2): t3 = Cells(lig, 3)
    If t1 <> "" And t2 <> "" And t3 <> "" Then
      For Each w In Worksheets
        col = Application.Match(w.Name, Rows(3), 0)
        If IsNumeric(col) Then
          t = Cells(lig, col)
          If t <> "" Then
            x = t1 & t2 & t3 & t
            Set P = w.Range("A3:D" & w.Range("A" & w.Rows.Count).End(xlUp).Row)
            tablo = P 'matrice, plus rapide
            For i = 1 To UBound(tablo)
              If x = tablo(i, 1) & tablo(i, 2) & tablo(i, 3) & tablo(i, 4) Then GoTo 1
            Next
            P(i, 1) = t1: P(i, 2) = t2: P(i, 3) = t3: P(i, 4) = t
          End If
        End If
1     Next w
    End If
  End If
Next Target
End Sub

Les noms en ligne 3 de la feuille "Base" doivent correspondre aux noms des feuilles.

A+

 

[job75]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Re,

J'ai modifié les macros du fichier (4) : les noms en ligne 3 doivent simplement correspondre aux noms des feuilles.

A+

 

[job75]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Re,

Elles sont très bien tes formules Modeste, mais elles ont l'inconvénient de toutes les formules :

- si l'on fait des tris sur la feuille "BASE" la colonne "DIVERS" (renseignée manuellement) ne suit pas

- en matriciel, si le tableau est grand, le temps de calcul devient un problème.

A+

 

[Si...]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

salut

En fait, je cherche un moyen pas trop compliqué et pas trop chronophage pour gérer les défraiements de mes bénévoles

Je reste à ta disposition pour t'apporter d'autres précisions et voir avec toi (ou d'autres) les solutions à envisager pour réaliser simplement ce projet

Ps Je suis un utilisateur amateur d'Excel, qui arrive à bidouiller des petites formules mais qui pige rien aux macros.

tu trouveras toujours quelqu'un pour t'aider au fils des piges.

A titre d'exemple, une autre façon de voir le problème à peu de Ko.

2 feuilles en tout et pour tout : l'une de saisies et l'autre pour éditer par nom (visualisation,impression...).

L'utilisation de l'outil Tableau permet de simplifier beaucoup de saisies (ajout, suppression, formatage de lignes, y compris copies de formules, listes de validation).

La simplicité des macros les met à disposition de tout programmateur même avec un léger bagage.

De plus on pourra ajouter un formulaire pour des saisies plus rapides, tout autre onglet pour des statistiques et graphes.

Les formulistes n'y sont pas exempts de propositions !

 

[association]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Salut Modeste, Job75, Si... et le forum,

Je cherchais des pistes pour résoudre mon problème et je me retrouve avec 3 solutions!!!
Un grand merci à tous et bravo pour votre compétence (et je ne parle même pas de votre réactivité)

Je viens de regarder rapidement mais il me semble que chaque solution correspond au fichier de mes rêves. A première vue, j'ai un petit faible pour celui de Modeste. Mais je teste tout ça et j'essaye de comprendre un peu la mécanique et je reviens vous en dire plus.

Mais encore MERCI ET BRAVO!!!

 

[job75]

Re : Copie d'une ligne dans un autre onglet si une colonne est complété

Bonjour association, Modeste, Si...,

Juste quelques remarques.

Bien comprendre que le code que j'ai donné est (un peu) compliqué pour une seule raison : il s'agit de conserver les données des colonnes "DIVERS" sur les bonnes lignes quelles que soient les modifications (suppression, tris) de la feuille "BASE".

J'ai testé le fichier (4) avec un tableau de 1000 lignes, toutes les dates étant différentes.

Sur Win XP - Excel 2003 les durées d'exécution sont les suivantes :

- entrée d'une donnée en colonne D E ou F => 0,01 à 0,03 seconde

- activation de la feuille "MADAME 2" (colonne E entièrement remplie) => 2,1 secondes.

Si l'on passe à 5000 lignes c'est bien plus long : 55 secondes pour l'activation.

Bonne journée et A+