VBA to insert the data into the first row of the table?

[cyrous0425]

Good day guys. My problem is every time I run the macro the data is always inserted at the end of my table. Is there a way to insert it always at first then move down? Here's the code by the way:

Private Sub Submit_Btm_Click()
Dim n As Long
With Sheets("Record")
    
    n = .Range("B:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    
    .Cells(n, "C") = TextBox1.Value
    .Cells(n, "J") = CDate(TextBox2.Value)
    .Cells(n, "K") = TextBox3.Value
    .Cells(n, "L") = TextBox4.Value
End With
End Sub

Cy
Thanks

 

[jmacleary]

Hi there. Assuming your table has headers in row 1 and data starts in row 2, this should do it:

Private Sub Submit_Btm_Click()
Dim n As Long
With Sheets("Record")

    .Rows("2:2").Insert Shift:=xlDown
    n = 2
    
    .Cells(n, "C") = TextBox1.Value
    .Cells(n, "J") = CDate(TextBox2.Value)
    .Cells(n, "K") = TextBox3.Value
    .Cells(n, "L") = TextBox4.Value
End With
End Sub

 

[jmacleary]

Hi there. I should have added the option to copy the formats from the line below, so the code needed is:

Private Sub Submit_Btm_Click()
Dim n As Long
With Sheets("Record")

    .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    n = 2
    
    .Cells(n, "C") = TextBox1.Value
    .Cells(n, "J") = CDate(TextBox2.Value)
    .Cells(n, "K") = TextBox3.Value
    .Cells(n, "L") = TextBox4.Value
End With
End Sub

 

[cyrous0425]

Thanks for the reply. I tried to run the code and its work but it copied the formatting of my header.

https://imgur.com/a/wFVqhE3

 

[jmacleary]

Sorry. Please see my second post (#3 above) - I forgot to tell it to copy format from below.

 

[cyrous0425]

Works Perfectly.

Thank You!