Discussion :

[JT0001]

Bonjour,
Je suis débutante en VBA..Voici ma question :
J’ai un fichier Excel avec plusieurs feuilles. Pour 5 feuilles, je dois pouvoir ajouter une ligne dans ses feuilles. Jusque-là j’ai créé la formule suivante (elle fonctionne bien) :
En résumé, cette formule me demande à quel numéro de ligne le système doit ajouter la nouvelle ligne vierge et le système l’ajoute au même endroit dans toute les feuilles sélectionnées par la MACRO.

MODULE 1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub ajout_ligne_par_feuilles()
 Dim s As Worksheet, ligne&
 ligne = InputBox("A quelle position voulez-vous insérer une nouvelle ligne?", "N° Ligne")
 
 For Each s In Worksheets
 Select Case s.Name
 Case "Retraité - Total", "Facturation - Ass.", "Facturation - SFM", "Retraité - Chèque", "Retraité - Paie"
 s.Rows(ligne).Insert Shift:=xlDown
 End Select
 Next s
 
 Application.CutCopyMode = False
 
 End Sub

Question :
Après avoir ajouté les lignes en question, je dois faire une copie incrémentée des formules de la ligne qui précède la ligne qui vient d’être ajoutée (copier vers le bas) s’il y a une formule, sinon il faudrait que la copie incrémentée se fasse à partir de la ligne suivante (copier vers le haut).

Pour les feuilles 1-2-3, je dois faire une copie incrémentée pour les formules de la ligne précédente ou suivante pour les colonnes A à O.

Pour la feuille 4, je dois faire une copie incrémentée pour les formules de la ligne précédente ou suivante pour les colonnes A à AA.

Pour la feuille 5, je dois faire une copie incrémentée pour les formules de la ligne précédente ou suivante pour les colonnes A, F, I, L et M seulement.

Enfin, si c’est possible, j’aimerais que la macro s’effectue immédiatement à la suite de l’ajout de la ligne dans le module 1 plutôt que de s’effectuer dans un autre module.

Merci pour votre aide!

[Theze]

Bonjour,

Regardes du coté de AutoFill pour un Range. Voici un petit exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Sub Test()
 
    Dim Ligne As Long
 
    Ligne = InputBox("A quelle position voulez-vous insérer une nouvelle ligne?", "N° Ligne")
 
    Rows(Ligne).Insert Shift:=xlDown
 
    'ici, c'est la formule située en colonne D qui est reprise, à adapter...
    Cells(Ligne - 1, 4).AutoFill Range(Cells(Ligne - 1, 4), Cells(Ligne, 4))
 
End Sub

[halaster08]

Bonjour,
Si j'ai bien compris, essaye ça:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Select Case s.Name
 Case "Retraité - Total", "Facturation - Ass.", "Facturation - SFM"
 s.Rows(ligne).Insert Shift:=xlDown
s.range("A" & ligne -1 & "O" & ligne - 1).copy
s.range("A" & ligne  & "O" & ligne ).pastespecial xlformulas
  Case "Retraité - Chèque"
 s.Rows(ligne).Insert Shift:=xlDown
s.range("A" & ligne -1 & "AA" & ligne - 1).copy
s.range("A" & ligne  & "AA" & ligne ).pastespecial xlformulas
  Case "Retraité - Paie"
 s.Rows(ligne).Insert Shift:=xlDown
s.range("A" & ligne -1 ).copy
s.range("A" & ligne  ).pastespecial xlformulas
s.range("F" & ligne -1 ).copy
s.range("F" & ligne  ).pastespecial xlformulas
'idem pour I,L,M
 End Select
 Next s

[Menhir]

En résumé, cette formule me demande à quel numéro de ligne le système doit ajouter la nouvelle ligne vierge

Ne serait-il pas plus ergonomique de demander que la ligne soit insérée là où se trouve la sélection.
Au lieu d'avoir une InputBox (ce qui représente une perte de temps et un risque d'erreur), tu n'aurais qu'à sélectionner l'une des cellules de la ligne en question et d'appuyer sur le bouton.
Ca peut se faire en remplaçant le début de ton code par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub ajout_ligne_par_feuilles()
   Dim s As Worksheet, ligne As Long
   ligne = Selection.Row

Après avoir ajouté les lignes en question, je dois faire une copie incrémentée des formules de la ligne qui précède la ligne qui vient d’être ajoutée (copier vers le bas) s’il y a une formule, sinon il faudrait que la copie incrémentée se fasse à partir de la ligne suivante (copier vers le haut).

Où se trouve "la formule" dans ta ligne ?

Qu'est-ce que tu entends par "incrémenté" ?
Est-ce qu'il faut ajouter un incrément à des valeur de la ligne copier ?
Est-ce qu'il faut modifier des références dans une formules ?
Est-ce que ces références sont absolues ou relatives (avec $ ou sans $) ?
Est-ce que ces références pointent sur des cellules de la ligne du dessus ou du dessous ?
Est-ce que l'incrément depuis la ligne du dessus est le même que celui depuis la ligne du dessous ?

Pour les feuilles 1-2-3, je dois faire une copie incrémentée pour les formules de la ligne précédente ou suivante pour les colonnes A à O.
Pour la feuille 4, je dois faire une copie incrémentée pour les formules de la ligne précédente ou suivante pour les colonnes A à AA.

Est-ce que ça veut dire qu'on doit éviter la copie des cellules au delà de ces colonnes ?
Est-ce que les cellules au delà de ces colonnes peuvent être copiées mais ne doivent pas être "incrémentées" ?

[JT0001]

Rebonjour,

Je vous remercie beaucoup pour vos réponses, toutefois je vais avoir encore besoin de votre aide s.v.p.!

Pour répondre aux questions de Menhir:

Où se trouve "la formule" dans ta ligne ?
pour les feuilles 1-2-3 les formules se trouvent dans les colonnes A à O
pour la feuille 4, il s'agit des colonnes A à AA
pour la feuille 2, il s'agit des colonnes A,F,I,L,M
Qu'est-ce que tu entends par "incrémenté" ?
Lorsque l'on clic sur la cellule à recopier et que l'on descend avec la petite croix en bas en droite de la cellule et cela permet de recopier la formule précédente avec les données de la ligne suivante. Par exemple: Dans la cellule D12 je retrouve la formule =E12*F12, si je copie la formule vers le bas à la cellule D13 je vais me retrouver avec la formule =E13*F13
Est-ce qu'il faut ajouter un incrément à des valeur de la ligne copier ?
Je ne comprends pas la question
Est-ce qu'il faut modifier des références dans une formules ?
Oui, la formule doit prendre en compte les données de la nouvelle ligne
Est-ce que ces références sont absolues ou relatives (avec $ ou sans $) ?
Sans $
Est-ce que ces références pointent sur des cellules de la ligne du dessus ou du dessous ?
Non
Est-ce que l'incrément depuis la ligne du dessus est le même que celui depuis la ligne du dessous ?
Oui, c'est seulement que si on ajoute une ligne au début du tableau, il n'y aura pas de formule sur la ligne du dessus, donc il faudra prendre la formule de la ligne suivante donc celle du bas.
Par contre, si cela rend la MACRO trop compliquée à faire... je peux ajouter une ligne vide à la première ligne et de cette façon la MACRO prendra toujours la ligne du dessous..cela pourrait me convenir..

Est-ce que ça veut dire qu'on doit éviter la copie des cellules au delà de ces colonnes ?
Exact, car cela peut être des données inscrites manuellement et on ne veut pas copier les informations inscrites manuellement.
Est-ce que les cellules au delà de ces colonnes peuvent être copiées mais ne doivent pas être "incrémentées" ?
Ce serait mieux si les formules incrémentées touchent uniquement les colonnes mentionnées plus haut

Merci beaucoup pour votre aide!

Pour répondre à halaster08

J'ai tenté la formule dans mon fichier, mais j'ai un message d'erreur qui mentionne "Erreur de Compilation: Next sans for"

Voici la formule que j'ai annexée à celle que j'avais au départ:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Sub ajout_ligne_par_feuilles()
 Dim s As Worksheet, Ligne&
 Ligne = InputBox("A quelle position voulez-vous insérer une nouvelle ligne?", "N° Ligne")
 
 For Each s In Worksheets
 Select Case s.Name
 Case "Retraité - Total", "Facturation - Ass.", "Facturation - SFM", "Retraité - Chèque", "Retraité - Paie"
 s.Rows(Ligne).Insert Shift:=xlDown
 End Select
 Next s
 
 Application.CutCopyMode = False
 
 Select Case s.Name
 Case "Retraité - Total", "Facturation - Ass.", "Facturation - SFM"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1 & "O" & Ligne - 1).Copy
s.Range("A" & Ligne & "O" & Ligne).PasteSpecial xlFormulas
  Case "Retraité - Chèque"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1 & "AA" & Ligne - 1).Copy
s.Range("A" & Ligne & "AA" & Ligne).PasteSpecial xlFormulas
  Case "Retraité - Paie"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1).Copy
s.Range("A" & Ligne).PasteSpecial xlFormulas
s.Range("F" & Ligne - 1).Copy
s.Range("F" & Ligne).PasteSpecial xlFormulas
s.Range("I" & Ligne - 1).Copy
s.Range("I" & Ligne).PasteSpecial xlFormulas
s.Range("L" & Ligne - 1).Copy
s.Range("L" & Ligne).PasteSpecial xlFormulas
s.Range("M" & Ligne - 1).Copy
s.Range("M" & Ligne).PasteSpecial xlFormulas
 End Select
 Next s

Pouvez-vous me dire ce que je n'ai pas fait correctement s.v.p.?

Merci beaucoup pour votre aide!

Pour répondre à Theze

Comme je ne suis pas très bonne en VBA, j'ai tenté d'adapter la formule que vous m'avez donné. Toutefois lorsque je mets en fonction la Macro, cela fonctionne pour une feuille seulement, soit celle qui est active. La Macro que je tente d'implanter doit créer les lignes dans toutes les feuilles mentionnées et doit copier les formules des colonnes précises pour chaque feuille.

Merci beaucoup pour votre aide!

[Menhir]

pour les feuilles 1-2-3 les formules se trouvent dans les colonnes A à O
pour la feuille 4, il s'agit des colonnes A à AA
pour la feuille 2, il s'agit des colonnes A,F,I,L,M

Tes données sont contradictoires : tu indiques deux méthodes différentes pour la feuille 2 (ligne 1 et ligne 3).
Je ne tiendrais donc pas compte de la troisième ligne pour la suite.

Lorsque l'on clic sur la cellule à recopier et que l'on descend avec la petite croix en bas en droite de la cellule et cela permet de recopier la formule précédente avec les données de la ligne suivante. Par exemple: Dans la cellule D12 je retrouve la formule =E12*F12, si je copie la formule vers le bas à la cellule D13 je vais me retrouver avec la formule =E13*F13

Si tu as mis des références relatives (sans $), ça se fait tout seul au cours de la copie.
A condition que ces formules ne fassent pas référence aux lignes au-dessous ou au-dessus.

si on ajoute une ligne au début du tableau, il n'y aura pas de formule sur la ligne du dessus, donc il faudra prendre la formule de la ligne suivante donc celle du bas.

Il est plus simple de copier la ligne se trouvant à l'endroit où on peut copier avant de faire la copie.

cela peut être des données inscrites manuellement et on ne veut pas copier les informations inscrites manuellement.

Dans ce cas, le plus simple est de les copier aussi (on copie toute les ligne) puis de les effacer.

En partant sur le principe que j'ai énoncé dans mon précédent message (on ne fait pas d'InputBox, on insère là où se trouve la sélection), ceci devrait faire ce que tu souhaites.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub ajout_ligne_par_feuilles()
   Dim s As Worksheet, ligne As Long
   Ligne = Selection.Row
 
   For Each s In Worksheets
      s.Rows(Ligne).Copy
      s.Rows(Ligne).Insert Shift:=xlDown
 
      Select Case s.Index
      Case 1 To 3
         s.Rows(Ligne).Range("P1:P" & Column.Count).ClearContents
      Case 4
         s.Rows(Ligne).Range("AB1:AB" & Column.Count).ClearContents
      End Select
   Next s
End Sub

[halaster08]

Bonjour,

Pour répondre à halaster08

J'ai tenté la formule dans mon fichier, mais j'ai un message d'erreur qui mentionne "Erreur de Compilation: Next sans for"

Voici la formule que j'ai annexée à celle que j'avais au départ:
Pouvez-vous me dire ce que je n'ai pas fait correctement s.v.p.?

Tu avais deux next pour un seul for, d'où le message d'erreur explicite.
L'insertion de ligne était déjà présente dans le code que je t'avais proposé, il fallait donc remplacer ton select case par le mien et non l'ajouter.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sub ajout_ligne_par_feuilles()
 Dim s As Worksheet, Ligne&
 Ligne = InputBox("A quelle position voulez-vous insérer une nouvelle ligne?", "N° Ligne")
 
 For Each s In Worksheets
 
 Select Case s.Name
 Case "Retraité - Total", "Facturation - Ass.", "Facturation - SFM"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1 & "O" & Ligne - 1).Copy
s.Range("A" & Ligne & "O" & Ligne).PasteSpecial xlFormulas
  Case "Retraité - Chèque"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1 & "AA" & Ligne - 1).Copy
s.Range("A" & Ligne & "AA" & Ligne).PasteSpecial xlFormulas
  Case "Retraité - Paie"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1).Copy
s.Range("A" & Ligne).PasteSpecial xlFormulas
s.Range("F" & Ligne - 1).Copy
s.Range("F" & Ligne).PasteSpecial xlFormulas
s.Range("I" & Ligne - 1).Copy
s.Range("I" & Ligne).PasteSpecial xlFormulas
s.Range("L" & Ligne - 1).Copy
s.Range("L" & Ligne).PasteSpecial xlFormulas
s.Range("M" & Ligne - 1).Copy
s.Range("M" & Ligne).PasteSpecial xlFormulas
 End Select
 Next s
end sub

[JT0001]

Bonjour,

Je vous confirme que la formule de Halaster semble me convenir!!
j'ai apporté la correction que vous m'avez mentionnée et je n'ai plus de message d'erreur!!
Pour votre information, j'ai apportée une modification supplémentaire puisque j'avais un erreur pour les plages à copier. J'ai donc mis une virgule entre les deux colonnes A à O et A à AA et cela fonctionne.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
Sub ajout_ligne_par_feuilles()
 Dim s As Worksheet, Ligne&
 Ligne = InputBox("A quelle position voulez-vous insérer une nouvelle ligne?", "N° Ligne")
 
 For Each s In Worksheets
 
 Select Case s.Name
 Case "Retraité - Total", "Facturation Ass.", "Facturation - SFM"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1, "O" & Ligne - 1).Copy
s.Range("A" & Ligne, "O" & Ligne).PasteSpecial xlFormulas
  Case "Retraité - Chèque"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1, "AA" & Ligne - 1).Copy
s.Range("A" & Ligne, "AA" & Ligne).PasteSpecial xlFormulas
  Case "Retraité - Paie"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1).Copy
s.Range("A" & Ligne).PasteSpecial xlFormulas
s.Range("F" & Ligne - 1).Copy
s.Range("F" & Ligne).PasteSpecial xlFormulas
s.Range("I" & Ligne - 1).Copy
s.Range("I" & Ligne).PasteSpecial xlFormulas
s.Range("L" & Ligne - 1).Copy
s.Range("L" & Ligne).PasteSpecial xlFormulas
s.Range("M" & Ligne - 1).Copy
s.Range("M" & Ligne).PasteSpecial xlFormulas
 End Select
 Next s
End Sub

Toutefois, j'ai remarqué que pour les cellules où la formule copiée concerne la feuille en cours, tout est parfait, par contre lorsque la formule copiée concerne une autre feuille, celle-ci ne se copie pas correctement.
Dans la feuille Retraité - Chèque à la cellule A21 on retrouve la formule ='Retraité - Paie'!A21.
Si je fais la MACRO et que j'ajoute une ligne à la cellule A20 on devrait retrouver la formule ='Retraité - Paie'!A20, mais celle que je retrouve actuellement est la même formule que la cellule A21.

Dans le fonds, il faudrait que la MACRO tienne compte que parfois la formule peut faire référence à une autre feuille à la même ligne et parfois qu'il s'agit d'un calcul entre deux cellules de la feuille active.

Pour terminer, petite demande spéciale qui me faciliterait beaucoup le travail. Comme la saisit des données se faire à la feuille Retraité - Paie, j'aimerais que lorsque je termine la MACRO qu'Excel m'amène directement à la cellule de la colonne B de la ligne qui vient d'être ajoutée. Donc, comme dans mon exemple, si j'ajoute la ligne 20, qu'Excel m'amène directement à la cellule B20 de la feuille Retraité - Paie pour entrer les nouvelles données.

Je vous remercie encore pour votre aide!

[halaster08]

Pour votre information, j'ai apportée une modification supplémentaire puisque j'avais un erreur pour les plages à copier. J'ai donc mis une virgule entre les deux colonnes A à O et A à AA et cela fonctionne.

My bad.
Attention si tu met une virgule il ne copie que les cellules A et O et non toutes les celulles entre A et O, il faut mettre ":" c'est un oubli de ma part.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sub ajout_ligne_par_feuilles()
 Dim s As Worksheet, Ligne&
 Ligne = InputBox("A quelle position voulez-vous insérer une nouvelle ligne?", "N° Ligne")
 
 For Each s In Worksheets
 
 Select Case s.Name
 Case "Retraité - Total", "Facturation - Ass.", "Facturation - SFM"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1 & ":O" & Ligne - 1).Copy
s.Range("A" & Ligne & ":O" & Ligne).PasteSpecial xlFormulas
  Case "Retraité - Chèque"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1 & ":AA" & Ligne - 1).Copy
s.Range("A" & Ligne & ":AA" & Ligne).PasteSpecial xlFormulas
  Case "Retraité - Paie"
 s.Rows(Ligne).Insert Shift:=xlDown
s.Range("A" & Ligne - 1).Copy
s.Range("A" & Ligne).PasteSpecial xlFormulas
s.Range("F" & Ligne - 1).Copy
s.Range("F" & Ligne).PasteSpecial xlFormulas
s.Range("I" & Ligne - 1).Copy
s.Range("I" & Ligne).PasteSpecial xlFormulas
s.Range("L" & Ligne - 1).Copy
s.Range("L" & Ligne).PasteSpecial xlFormulas
s.Range("M" & Ligne - 1).Copy
s.Range("M" & Ligne).PasteSpecial xlFormulas
 End Select
 Next s
end sub

Dans le fonds, il faudrait que la MACRO tienne compte que parfois la formule peut faire référence à une autre feuille à la même ligne et parfois qu'il s'agit d'un calcul entre deux cellules de la feuille active.

C'est normalement le cas, le code que je t'ai fournit insère une ligne puis fait un copier-coller de la formule, référence à une autre feuille comprise, je viens de le tester sur un classeur et ça fonctionne.

Pour terminer, petite demande spéciale qui me faciliterait beaucoup le travail. Comme la saisit des données se faire à la feuille Retraité - Paie, j'aimerais que lorsque je termine la MACRO qu'Excel m'amène directement à la cellule de la colonne B de la ligne qui vient d'être ajoutée. Donc, comme dans mon exemple, si j'ajoute la ligne 20, qu'Excel m'amène directement à la cellule B20 de la feuille Retraité - Paie pour entrer les nouvelles données.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

sheets("Retraité - Paie").range("B"& ligne).select