Macros to insert a new row

lbradbury

New Member
Joined
May 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a sheet that has rows of data, in which they are not in a table. There are formulas within the rows, and currently we manually insert a row so the formulas copy to the newly inserted row.

I would like to automate this process. The issue I'm posed with is that the row numbers are variable and change as new records are entered. Below is a snipit of the sheet. The intention is to insert a row about the green line, but as the new data is inserted the row number will change.


1602678274766.png
 

Attachments

  • 1602678109990.png
    1602678109990.png
    11.9 KB · Views: 9

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Puertorekinsam

Active Member
Joined
Oct 8, 2005
Messages
287
This will check the cell above the one you click in, as long as that cell is not blank, it will auto populate the 8 cells from A to H with the formula from the cell above it

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MyRow As Integer
Dim MyCol As Integer
Dim i  As Integer

MyRow = Target.Row
MyCol = Target.Column
If MyRow <> 1 Then
    If Cells(MyRow - 1, MyCol) <> "" Then

        
        For i = 1 To 8
        
           Cells(MyRow - 1, i).Copy Cells(MyRow, i)
        Next

    
    End If
End If
End Sub
 

lbradbury

New Member
Joined
May 14, 2020
Messages
25
Office Version
  1. 365
Platform
  1. Windows
This will check the cell above the one you click in, as long as that cell is not blank, it will auto populate the 8 cells from A to H with the formula from the cell above it

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MyRow As Integer
Dim MyCol As Integer
Dim i  As Integer

MyRow = Target.Row
MyCol = Target.Column
If MyRow <> 1 Then
    If Cells(MyRow - 1, MyCol) <> "" Then

       
        For i = 1 To 8
       
           Cells(MyRow - 1, i).Copy Cells(MyRow, i)
        Next

   
    End If
End If
End Sub



Thank you for this! I should have mentioned that the data is being inputted into a form within another worksheet, when the macros button is pressed the data it so be inserted in the row of another worksheet. Does that make sense?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,774
Messages
5,544,142
Members
410,595
Latest member
Tatum2020
Top