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 Mentor Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    475
    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

  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
  •