Insérer plusieurs lignes en dessous de la dernière saisie et copier

VBA
1

Bonjour,

Je bricole le VBA avec très peu de connaissances, pourriez-vous m’aider SVP ?

Sur mon tableau joint de la feuil1

J’aimerai avec une macro, insérer 10 lignes avec leurs formules et ce, à partir d’une ligne en dessous de la dernière ligne saisie.

Dans cet exemple à partir de la ligne 25. (De manière à laisser la ligne N° 24 vide).

De plus, copier à partir de cette ligne 25 les lignes N° 2, 3, 4 et 5 de la feuil2.

Ce tableau est évolutif, donc dans quelques temps la ligne 25 pourra très bien être la ligne 76. Il faut donc revenir à chaque fois sur la dernière ligne saisie + 1.

Merci par avance de votre aide.

Bien cordialement
Test.xlsm (14,9 Ko)

2 
Sub GIL()
     With Sheets("Feuil1")
          Set c = .Range("A" & Rows.Count).End(xlUp).Resize(, 11)     'derniere cellule A & 11 colonnes
          Set c1 = c.Offset(1).Resize(10)    'ligne suivante & 10 lignes

          c.Copy c1                          'copier dernière lignes et coller 10 fois
          On Error Resume Next
          c1.SpecialCells(xlConstants).ClearContents     'supprimer toutes les cellules avec contenu fixe (donc laisser les formules)

          For Each ar In Sheets("feuil2").Range("A2:K5").SpecialCells(xlConstants).Areas     'toutes les cellules afec contenu fixe
               ar.Copy                       'copie area individuellement
               c1.Cells(ar.Row, ar.Column).PasteSpecial xlValues     'coller
          Next
          On Error GoTo 0
     End With
End Sub
3

:+1:

Juste une petite chose que je n’ai pas précisé dans mon 1er message.

Si toutefois je fais évoluer mon tableau, serait-il possible qu’il copie également les formules des colonnes de A à G

Exemple, si je saisie une formule dans les cellules A3, B3, C3, D3, F3 et G3, peut-il les recopier ?

Et i3 aussi

merci

4

Merci d’oublier mon dernier message.

Je pense qu’il y a une petite modif à faire mais pas celle-ci.

Je reviendrais vers vous demain

Passez une belle soirée

5

Bonjour,

Je reviens vers vous concernant mon tableau.

Je pense que vous avez bien effectué ce que je souhaitais. C’est moi qui me suis mal expliqué la première fois.

J’ai noté mes nouvelles explications dans le tableau joint.

En fait, il faut insérer des lignes au-dessus de la dernière ligne afin que mes cellules en ligne 1 se mettent à jour.

Si vous pouviez modifier, ça m’arrangerait

Merci par avance

Test.xlsm (22,7 Ko)

6

MO a créé des tableaux structuré pour faciliter ce genre de problèmes.
Macro « Gil2 »
Test (68).xlsm (34,9 Ko)

Sub GIL2()
     Dim LO1, LO2
     Set LO1 = Sheets("Feuil1").Range("A2").ListObject     'ce tableau de la feuil1
     Set LO2 = Sheets("Feuil2").Range("A1").ListObject     'ce tableau de la feuil2
     r = LO2.ListRows.Count                  'nombre de lignes du tableau 2

     With LO1                                'avec le 1er tableau
          .ListRows.Add                      'ajouter une ligne vide (avec formules et formats)
          With .ListRows.Add.Range           'ajouter une 2eme ligne vide (...)
               Set c = .Resize(r)            'nombre de lignes sera le nombre du tableau2
               For i = 1 To c.Columns.Count  'boucler les colonnes
                    If Not c.Cells(1, i).HasFormula Then     'c'est une colonne sans formule
                         c.Columns(i).Value2 = LO2.DataBodyRange.Columns(i).Value2     'copier et coller le contenu de cette colonne du tableau2 en tableau1
                    End If
               Next
          End With
          With .ListRows.Add.Range           'ajouter encore une ligne vide
               Application.Goto .Cells(1)    'se positionner dans cette cellule
          End With
     End With

End Sub
7

Merci c’est exactement ce que j’attendais, sauf que je préfèrerais l’avoir sur un tableau non structuré car ensuite je l’adapterai sur un autre classeur existant que je ne peux plus structurer.

Si ce n’est pas pour aujourd’hui, ce n’est pas grave.

Merci

8

Test (68).xlsm (32,5 Ko)

9

La manip d’un tableau à l’autre est nickel
Mais les totaux de la ligne 1 ne correspondent pas au total des colonnes

10

dans la macro « Gil2 » on a ceci, sans cette virgule et ce « 1 » en rouge :thinking:, donc ajoutez cela

image
image825×35 2.02 KB

11

Bonjour,

Désolé de vous déranger à nouveau et merci pour votre dernière réponse mais je ne comprends rien à cette formule des totaux DECALER.

Dans le tableau ci-contre, si je modifie une quantité, les totaux ne changent pas

Si je lance la macro, elle fonctionne très bien

Test (68) (3).xlsm (24,8 Ko)
*

Pouvez-vous m’aider à comprendre ce souci ?

Merci par avance.

12

bonsoir, la macro actualise la plage nommée « Gil » qui commence juste en dessous l’entête de la colonne A (défini par le variable « Début » = 12 pour le moment et la derniere ligne. Cela se fait avec cette ligne de la macro

          .Cells(Début, "A").Resize(c3.Row + r2 + 2 - Début).Name = "Gil" '>>>> plage nommée dans colonne A pour vos formules

Cette plage est par exemple A12:A30, pour faire le subtotal de la colonne F = 6eme colonne, on doit décaler la plage « Gil » 5 colonnes vers droit et donc dans la formule de F4 (et les autres cellules à côté) =SOMME(DECALER(Gil;;COLONNE()-1;;)) .
Test (68) (3).xlsm (33,3 Ko)

13

Bonjour,

Encore une fois merci.

Je savais que sans connaissance particulière, j’allais être perdu un jour, et là je le suis. Pourtant j’aime bien comprendre le minimum au cas où j’aurai une petite modif à apporter un jour. Là, Je n’arrive pas à trouver cette fameuse plage Gil. Bref, je ferai sans parce que là ça fonctionne très bien.

Je dois réaliser 2 autres fichiers identiques.

Sauf que sur le 1er fichier la feuil2 commencera en A4 pour aller jusqu’en Q33 (voir fichier joint)

Dans le 2ème fichier la feuil2 commencera en A4 pour aller jusqu’en Q19.

Si ce n’est pas trop compliqué, pouvez vous me dire à quel endroit je devrais faire ces modifs pour qu’elles soient prises en compte ?

Dernière question qui n’a rien à voir mais je profite de votre gentillesse :

Quelle est la formule la plus simple en VBA pour aller à la dernière ligne en colonne A et insérer quelques lignes ?

Merci

Vous êtes très doué dans votre taf :+1:
Test (68) (3) (1).xlsm (25,8 Ko)

14

pour ces plages nommées, j’éspères que ceci aide à comprendre

15

Test (68) (3) (1).xlsm (37,0 Ko)

16

Je reçois effectivement des mails d’Alex d’Excel formation dont celui-ci.

Je pense maitriser cette fonction… Seulement sur votre fichier je ne vois pas la plage nommée :joy::joy:.

•Est-ce vous qui avez ajouté le Module2 ? Si oui, à quoi peut-il me servir ?

•Sur le fichier joint à votre dernier message les sous-totaux ne fonctionnent plus.

•Dans le Module1 > Macro1, je ne comprends pas lorsque vous parlez de choisir entre la méthode 1 et 2 alors que les 2 méthodes sont actives.

• Je ne vois pas comment faire pour sélectionner le tableau de la Feuil2 en copiant l’intégralité de ce tableau de A4 à Q33.

•Vous me faite peur lorsque vous dites ‘’ attention n’utiliser pas des filtrer’’ j’ai un des mes 2 tableaux auquel j’aimerai ajouter ces ajouts de lignes qui à déjà une liste déroulante activeX qui crée des filtres.

Pour quelqu’un de basique comme moi, n’y a-t-il pas moyen de faire plus simple, du style :

•Aller à la dernière ligne avec : ActiveSheet.Cells(Rows.Count, « B »).End(xlUp)(1).Select

•Copier cette dernière ligne

•Insérer 30 lignes.

Coller la dernière ligne dans ces 30 lignes, soit : Formules et format mais pas les valeurs saisies.

• Aller copier le tableau de la Feuil2 et le coller dedans

Lorsque je fais tout ça sans macro, ça fonctionne et le fait d’insérer les 30 lignes, actualise mes totaux (en haut de la feuille1)

Ce n’est qu’une simple question.

Je suis sincèrement navré de vous déranger à ce point, je pensais que ça allait être plus simple.

17

zut, cette plage, l’adresse était correcte mais dans la « feuil2 », donc faux :face_with_spiral_eyes:
Vous devez trouver « Gil » dans le ruban sous « formules>Gestionnaire de noms » pour vérifier si elle est maintenant dans la feuil1.
image

Et oui, le module2, c’était moi avec l’enregistreur de macros et il ne sert à rien.
Les sous-totaux, comme je l’ai dit ici en haut, la plage nommée utilisait la fausse feuille.

Les 2 méthodes, je pense que vous choisirez la 1, donc vous pouvez supprimer ces lignes de méthode 2 (de nombreux chemins mènent à Rome).

     Dim Derligne
     With Sheets("Feuil2")
          Derligne = .Range("A" & Rows.Count).End(xlUp).Row     'numéro de la dernière cellule A de la feuil2
          With .Range(.Range("A4"), .Range("Q" & Derligne))
          ....

Attention avec une construction « With … End With », le point devant le « range » fait appèl à cette construction, donc c’est important de bien visualiser cela ! Donc, le "with .range(…) utilise la feuil2 et puis les 2 cellules entre les parenthèses aussi. Ces 2 cellules se sont la cellule en haut et gauche et la cellule en bas et droite.

Ce filtre : supposons qu’il y a des données jusqu’à A20 mais le filtre cache les lignes 11-20, alors ce "Derligne = .Range(« A » & Rows.Count).End(xlUp).Row " vous donnera 10 et ne pas 20 !!! Il faut le savoir, c’est pourquoi utiliser des tableaux structuré est si facile, il ne faut pas s’occuper de tout ça, on utilise l’objet « Listobject » et tout va tout seul.

18

Et alors la méthode simple avec macro « Gil_3 » et en espérant que les formules de Feuil2 sont okay.
Test (68) (3) (1).xlsm (36,1 Ko)

19

Mes explications sont en pièces jointes
Très belle soirée à vous
Comptes.xlsm (550,5 Ko)
Explications.pdf (202,0 Ko)

20

Vos formules vont jusqu’à la ligne 39, donc il y a encore quelque lignes vides.
On insère à la ligne 36 (pour tester, j’ai ajoute le couleur jaune à A36 et un « xxx » dans B36, donc ces cellules doivent se déplacer en dessous notre nouvelle plage et rester inchangé !!!)
Dans la feuille « SaisieFixe1 », vous avez des choses dans les lignes 107-108, donc ce « .Range(« A » & Rows.Count).End(xlUp).Row » s’arrête à A108 au lieu de A13. Donc on commence à A106 >>> .Range(« A106 »).End(xlUp).Row
On regarde colonne par colonne, s’il y a une formule, si oui, on laisse la formule, si non, on copie et colle la colonne correspondante de « SaisieFixe1 » (il y avait des erreurs dans les formules des « SaisieFixe1 », donc …)

J’attends votre affirmation pour l’autre, ou vous pouvez essayer de le faire …
Comptes (1).xlsm (516,5 Ko)