Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:




    Code:
    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

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    624
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert the data into the first row of the table?

    Hi there. Assuming your table has headers in row 1 and data starts in row 2, this should do it:
    Code:
    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
    Last edited by jmacleary; Feb 7th, 2019 at 03:31 AM.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    624
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert the data into the first row of the table?

    Hi there. I should have added the option to copy the formats from the line below, so the code needed is:
    Code:
    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
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  4. #4
    New Member
    Join Date
    May 2018
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert the data into the first row of the table?

    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
    Last edited by cyrous0425; Feb 7th, 2019 at 03:56 AM.

  5. #5
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    624
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert the data into the first row of the table?

    Sorry. Please see my second post (#3 above) - I forgot to tell it to copy format from below.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  6. #6
    New Member
    Join Date
    May 2018
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert the data into the first row of the table?

    Works Perfectly.

    Thank You!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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