Consulting

Results 1 to 3 of 3

Thread: Copy paste first row with correct cell reference

  1. #1
    VBAX Regular
    Joined
    Jan 2016
    Posts
    10
    Location

    Copy paste first row with correct cell reference

    When I use this VBA code, I copy the first row (with many formulas, like one in B1 that says = A1*2) and add rows where my marker is. In cell A2 I specify how many rows I want to be added.

    Sub addnewrows()
    
    With ActiveCell.Resize([A2]).EntireRow
      .Insert
      .Offset(-[A2]).Value = Rows(1).FormulaR1C1
    End With
    
    End Sub

    If A2 equals 3, the macro adds 3 rows.
    If my mouse is on row 10, then new rows will be added to 10, 11 and 12.

    Problem is that cell reference in row 11 and 12 is not correct.

    Row 10 has formula A10 * 2 (correct)
    Row 11 has formula A12 * 2 (not correct)
    Row 12 has formula A14 * 2 (not correct)

    I would like the formula number to equal row number

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    533
    Location
    Hello jokris,

    If you rewrite your macro this way...
    Sub addnewrows()
         
        Dim Rng As Range
        
            Set Rng = ActiveCell.EntireRow
            Set Rng = Rng.Resize(RowSize:=Range("A2"))
            
            Rng.Columns(2).FormulaR1C1 = "=RC[-1]*2"
         
    End Sub
    It will work.
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    VBAX Regular
    Joined
    Jan 2016
    Posts
    10
    Location
    Sorry it did not work. My cell * 2 was just an example, the whole first row includes many cells with different formulas.

    When trying the code with three rows inserted into row 10, it replaced the value of cell B10, B11 and B12 but it didn't insert new rows.
    I would like all columns with all codes and formatting in my first row to be copied and inserted into new rows.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •