Insertion de ligne(s) avec copie de certaines valeurs et formules

Bonjour le forum,

En complément à ma question « Ajout de lignes dans un tableau par rapport à la valeur d’une colonne », je me suis aperçu que la macro « Private Sub Worksheet_Change(ByVal Target As Range) » de la Feuil2 n’était pas tout à fait efficiente dans son travail d’insertion que j’attendais.

En effet il faudrait finalement que lorsque l’insertion de la ou des nouvelles lignes (qui se fait très bien lorsque je change la valeur de la colonne « I ») que le contenu des colonnes (A à BA) de la dernière ligne (avant insertion) soit dupliqué (formules et texte) à l’exception des informations (quand il y en a) des groupes de colonnes (K à O), (Q à S), (W à X), la colonne (AE) et enfin les colonnes (AG à AH). Il faudrait également que la colonne « A » et « E » ne soient pas fusionnées afin que je puisse faire des filtres dessus.

Je ne sais pas si j’ai été clair. J’ai mis en rouge l’entête des colonnes dans lesquelles il faut que les cellules restent vides et en commentaire les deux colonnes qui ne doivent pas être fusionnées après l’insertion.

Ci-dessous la partie de la procédure déjà en place pour l'insertion des lignes :

Option Explicit

Dim I_adresse As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, d, t, i&, derln&, c&, nb&, val&

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    '// ajout lignes selon nombre entré en colonne I
    If Not Intersect(Target, Me.Columns("I")) Is Nothing And Target.Count = 1 Then
        If Me.Cells(Target.Row, "A") <> Empty Then
        If MsgBox("Attention la valeurs de la colonne I a été modifiée. Voulez-vous continuer ?", vbYesNo) = vbNo Then Exit Sub
           Application.EnableEvents = False
           For i = Target.MergeArea.Rows.Count + 1 To Target.Value
                Target.MergeArea.Offset(1).EntireRow.Insert

                With Target.Offset(1).Offset(, 1)
                    .Value = "N°" & i
                    .Resize(, 44).Borders(xlEdgeTop).LineStyle = xlDot
                End With

                For c = 0 To Target.Column - 1
                    nb = i - Target.Offset(, -c).MergeArea.Rows.Count
                    Range(Target.Offset(, -c), Target.Offset(, -c).Offset(nb)).MergeCells = True
                Next c
            Next i
            Application.EnableEvents = True
        End If
    End If

    '// ajustement nombre en colonne I selon nombre de lignes
    If I_adresse <> Empty Then
        If IsNumeric(Me.Range(I_adresse)) Then
            Application.EnableEvents = False
            Me.Range(I_adresse) = Me.Range(I_adresse).MergeArea.Rows.Count
            Application.EnableEvents = True
        End If
    End If

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

  End Sub

Cordialement.

Bonjour,

Je n'ai regardé de près ni ton fichier, ni ta macro, mais l'intitulé de ton sujet m'amènes à penser que tu te compliques beaucoup la vie en passant par macro... Alors qu'un tableau structuré permet de reporter automatiquement les formules et la mise en forme lors de l'ajout de lignes.

Salut Pedro22,

Dans le principe tu as raison mais dans mon cas ça ne marche pas car il y a des informations qui doivent se répercuter (nom de la formations, dates, n° des candidats etc..) sur la ou les nouvelles lignes insérer. Avec un tableau comme tu dis il n'y aurait uniquement que les formules qui s'impacteraient mais cela dit ton intervention est pertinent et je te remercie d'avoir apporté une réponse à ma question.

Cordialement.

Bonjour le forum,

Sur ma question j'ai pu avancer. Lorsque je modifie la valeur de la colonne "I" les nouvelles lignes qui sont créées prennent bien les valeurs pour les colonnes dans lesquelles je souhaitais voir copiées les informations de la ligne supérieur. Pour cela j'ai modifié la macro

'// ajout lignes selon nombre entré en colonne I
    If Not Intersect(Target, Me.Columns("I")) Is Nothing And Target.Count = 1 Then
        If Me.Cells(Target.Row, "A") <> Empty Then
        If MsgBox("Attention la valeurs de la colonne I a été modifiée. Voulez-vous continuer ?", vbYesNo) = vbNo Then Exit Sub
           Application.EnableEvents = False
           For i = Target.MergeArea.Rows.Count + 1 To Target.Value
                Target.MergeArea.Offset(1).EntireRow.Insert

                'Ajoute le nouveau numéro des places dans la colonne J
                With Target.Offset(1).Offset(, 1)
                    .Value = "N°" & i
                    .Resize(, 44).Borders(xlEdgeTop).LineStyle = xlDot
                End With

                'Copie les informations de la colonne P
                With Target.Offset(1).Offset(, 7)
                    .Value = Target.Offset(1).Offset(-1, 7)
                End With

                'Copie les informations des colonnes S à U
                With Target.Offset(1).Offset(, 23)
                    .Value = Target.Offset(1).Offset(-1, 23)
                End With

                    'Copie les informations des colonnes S à U
                    For val = 10 To 12
                        With Target.Offset(1).Offset(, val)
                            .Value = Target.Offset(1).Offset(-1, val)
                        End With
                    Next val

                    'Copie les informations de la colonne AF
                    With Target.Offset(1).Offset(, 23)
                        .Value = Target.Offset(1).Offset(-1, 23)
                    End With

                    'Copie les informations des colonnes AJ à AM
                    For val1 = 27 To 30
                        With Target.Offset(1).Offset(, val1)
                            .Value = Target.Offset(1).Offset(-1, val1)
                            .WrapText = False
                        End With
                    Next val1

                'Fusionne les colonnes A à I
                For c = 0 To Target.Column - 1
                    nb = i - Target.Offset(, -c).MergeArea.Rows.Count
                    Range(Target.Offset(, -c), Target.Offset(, -c).Offset(nb)).MergeCells = True
                Next c

                'Fusionne la colonne V
                    nb = i - Target.Offset(, 13).MergeArea.Rows.Count
                    Range(Target.Offset(, 13), Target.Offset(, 13).Offset(nb)).MergeCells = True
                'Next c

            Next i

            'Enlève la fusion des colonnes A et E
            détail_de_la_colonne_A_et_E

           Application.EnableEvents = True
        End If
    End If

Maintenant il faudrait qu'on me dise COMMENT je peux copier les formules des colonnes Z à AD . J'ai essayé la ligne de codes

 'Copie des formules des colonnes Z à AD
                    For val2 = 17 To 21
                        With Target.Offset(1).Offset(, val2)
                            .Value = Target.Offset(1).Offset(-1, val2)
                            .WrapText = False
                        End With
                    Next val2

mais cela me donne que la valeur et non pas la formule de la ligne supérieur ce qui est logique puisque j'utilise "value =".

Existe-t-il un code pour les formules que je pourrais mettre à la place de "value =" ?

En PJ je mets le nouveau fichier avec les modifications apportées à la macro « Private Sub Worksheet_Change(ByVal Target As Range) » de la Feuil2.

Cordialement

As-tu essayé une instruction du style :

Range(A1).Formula = Range("A2").Formula

Bonjour,

Je n'ai pas trouvé où se trouvait le programme pour copier les formules, car tu as beaucoup de programmes, et que tu n'as pas précisé où le trouver, pour copier les formules il faut passer par formula, et non par value qui est la valeur, résultat du calcul de la formule, essaye de remplacer par ça :

                    For val2 = 17 To 21
                        With Target.Offset(1).Offset(, val2)
                            .FormulaR1C1 = Target.Offset(1).Offset(-1, val2).FormulaR1C1
                            .WrapText = False
                        End With
                    Next val2

Sinon quand je cherchais ton programme j'ai remarqué quelque chose d'horrible...

Sub detaildetouteslescolonnes()

Call DetaillerLaColonneA
Call DetaillerLaColonneB

Call DetaillerLaColonneC
Call DetaillerLaColonneD

Call DetaillerLaColonneE
Call DetaillerLaColonneF
Call DetaillerLaColonneG
Call DetaillerLaColonneH
Call DetaillerLaColonneI
Call DetaillerLaColonneV

End Sub

Tu as fait 10 programmes qui font exactement la même chose avec simplement un paramètre qui change...

Pourquoi ne pas traiter toutes tes colonnes en un programme? Ou à la limite faire une boucle où tu changes juste la lettre de la colonne avec une variable colonne?

Il y peut-être d'autres choses à revoir dans ton fichier mais ça c'est à toi de te demander si ce que tu as fait est vraiment bien, mais si tu retrouves le même programme plusieurs fois, c'est que tu peux forcément faire mieux, si tu as juste un paramètre qui change, passe le en variable ou trouve un moyen de regrouper tes programmes

Pour Ausecour :

Super ton code ça marche. J'avais essayé avec ".FormulaR1C1 = Target.Offset(1).Offset(-1, val2)" mais sans mettre à la fin ".FormulaR1C1" comme quoi il ne faut pas grand chose . Merci encore.

Au sujet de :

Sub detaildetouteslescolonnes()

Call DetaillerLaColonneA
Call DetaillerLaColonneB

Call DetaillerLaColonneC
Call DetaillerLaColonneD

Call DetaillerLaColonneE
Call DetaillerLaColonneF
Call DetaillerLaColonneG
Call DetaillerLaColonneH
Call DetaillerLaColonneI
Call DetaillerLaColonneV

End Sub

J'ai fait cette macro car je ne savais pas comment m'y prendre autrement pour enlever la fusion de toutes les colonnes donc je suis preneur pour une solution plus efficiente.

Pour Pedro22 :

J'avais pensé à ta solution :

Range(A1).Formula = Range("A2").Formula

mais je ne savais pas comment l'intégrer dans ma macro.

Merci à vous deux pour avoir pris le temps de me répondre et de m'avoir trouver des solutions. Je mets le sujet en résolu.

Cordialement.

Tout d'abord, merci d'avoir passé le sujet en résolu

Pour ta question, quand je veux enlever la fusion de toutes les cellules de 3 colonnes par exemple, je sélectionnes les 3 colonnes et clique sur fusionner les cellules, ça enlève tout. Essaye de faire la même chose avec la fonction unmerge?

Essayes range("a:v").unmerge

Merci pour ta réponse sur comment enlever le fusionnement des cellules mais cette solution ne peut pas s'appliquer à mon tableau car je souhaite que la 1ère ligne "défusionnée" soit recopiée pour la formation concernée afin de pouvoir faire des filtres dessus. Par exemple pour la colonne F qui est le lieu de la formation si j'enlève la fusion je me retrouve avec uniquement la 1ère ligne de chaque formation rempli. Si je veux faire un filtre sur PARIS je n'aurai que deux lignes or qu'en vérité il y a 6 lignes.

Les différentes macro

Call DetaillerLaColonneA

Call DetaillerLaColonneB

Call DetaillerLaColonneC

Call DetaillerLaColonneDand

etc font ce travail.

Cordialement.

Rechercher des sujets similaires à "insertion ligne copie certaines valeurs formules"