View original (French)

Insert a row below and copy using autofill

Solved
eltchico -  
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   -
Hello,

I regularly consult your forum and find a lot of answers, and for that, thank you, but right now I'm stuck in a "loop," if I may say so.
Here is my need:
I want to insert a row below the selection, for which I am able to do it
ActiveCell.Range("A2").EntireRow.Insert Shift:=xlDown

From there, however, my problem begins: I would like to copy and paste (dragging thus autofill) only the active row cells from columns C to I.
I have tried writing
ActiveCell.Range("A2").EntireRow.Insert Shift:=xlDown
ActiveCell.Range("C1:I1").Select
Selection.AutoFill Destination:=ActiveCell.Range("C1:I2"), Type:=xlFillDefault
ActiveCell.Range("C1:I2").Select

Problem
1/ ActiveCell.Range("C1:I1").Select selects from the 3rd to the 9th column from the active cell which is my reference (activeCell) so if I am not in column A for ActiveCell it does not work (for example, if my active cell is in column C then ActiveCell.Range("C1:I1") selects columns E to K instead of C to I as I would like.

2/ Selection.AutoFill Destination:=ActiveCell.Range("C1:I2"), Type:=xlFillDefault
The autofill method of the range class has failed

Could you please help me so that no matter the active cell, it selects the cells from columns C to I of that active row to drag down in the newly inserted row

The goal is to keep automatic increments like Monday Tuesday... or 1.1 would become 1.2, etc...

Thank you in advance.

Configuration: Windows 7 / Chrome 54.0.2840.99

4 answers

  1.
    ccm81 Posted messages 11033 Status Member 2 434
     
    Hello

    try this 

    Range("A2").EntireRow.Insert Shift:=xlDown
    Range("C1:I1").Select
    Selection.AutoFill Destination:=Range("C1:I2"), Type:=xlFillDefault
    Range("C1:I2").Select


    Best regards
    0
  2. eltchico26 Posted messages 3 Registration date   Status Member Last intervention  
     
    Hello,

    I don't have the same username because I had to create a member account in order to respond, but it's really me.

    Thank you for the reply, however, writing Range("A2") will systematically insert a row in relation to cell A2 of my sheet, whereas I want to insert a row below the active row.

    For example, if I'm on row 9, I want to insert a row below row 9, that's why I wrote ActiveCell.Range("A2").

    The issue is that if a cell in column D is selected, VBA understands that my ActiveCell reference is the first column.

    I'm trying to find an equivalent to ActiveCell, something like ActiveRow, but apparently it doesn't exist.

    Thanks again for trying; if there are any other suggestions...
    0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello Eltchico, CCM

    What is the purpose of range("A2")?

    Because
    ActiveCell.EntireRow.Insert Shift:=xlDown
    inserts a row below the active cell's row regardless of its column

    You can also write
    Rows(ActiveCell.Row + 1).Insert

    For C1:I1
    you can use the offset function 
    Dim ligne As Byte
    ligne = ActiveCell.Row
    Rows(ligne + 1).Insert
    Range(Cells(ligne, "C"), Cells(ligne, "I")).Copy ActiveCell.Offset(1, 0)

    Michel
    0
    1. eltchico26 Posted messages 3 Registration date   Status Member Last intervention  
       
      RE,

      First of all, thank you for your quick responses.
      Michel_m, I managed to sort things out between what you suggested and my base code to finally arrive at this:

      Public Sub insertionligneCCM2()
      Dim ligne As Byte
      ligne = ActiveCell.Row
      Rows(ligne + 1).Insert
      Range(Cells(ligne, "C"), Cells(ligne, "I")).Select
      Selection.AutoFill Destination:=Range(Cells(ligne, "C"), Cells(ligne + 1, "I")), Type:=xlFillDefault
      End Sub

      And now I have everything I want.
      No matter which cell is selected in the active row, it correctly inserts a line below and then performs a copy/drag (Autofill) of cells C to I from the active row into the active +1 row.

      Be careful michel_m, activeCell.EntireRow.Insert Shift:=xlDown inserts a row above the active row and not below.


      As for answering your question, my Range("A2") was used as a reference to indicate to insert a line below A1 (reference of my active row), but I am quite a novice in VBA so I'm doing the best I can at the beginning...

      With this help, thank you again.
      For me, the subject is closed.
      Hoping it will be useful to others.
      0
  4. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Attention michel_m, activeCell.EntireRow.Insert Shift:=xlDown inserts a row above the active row and not below.

    Nonsense!!!

    Hoping it's useful to others
    Definitely not! Very bad code
    Michel
    0
    1. eltchico26 Posted messages 3 Registration date   Status Member Last intervention  
       
      Hello

      sorry michel_m but when I test activecell.entirerow.insert etc... it inserts a row above instead of below.

      However, if you think this is a very bad code then
      1/ I would like to understand why in order to improve myself
      2/ I am open to improving it further if you don’t mind.
      0
      1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320 > eltchico26 Posted messages 3 Registration date   Status Member Last intervention  
         
        Hello
        I wrote 
        line = ActiveCell.Row
        Rows(line + 1).Insert


        and not 
        activecell.entirerow.insert ....


        in other words, is it useless to help you?
        0