Inserting new row of data in excel, every time user click on command button

Question

I have started learning VBA programming and thought of creating one small application for inserting student details into an Excel sheet. In one Excel sheet named "Main", I have created a form to take user inputs and in another sheet named "Database" I am trying to insert a row of records every time a user clicks the button.

I am successfully able to insert one row of data i.e. first row of data in the database sheet, but my problem is - I want to go to the next row in the sheet once user enters the record and clicks on the button a second time. Similarly for the third time and so on.

My code is:

Private Sub CommandButton1_Click()
Dim i As String
Dim j As String
Dim k As String
Dim l as integer
i = Sheets("Main").Cells(1, 2).Value
j = Sheets("Main").Cells(2, 2).Value
k = Sheets("Main").Cells(3, 2).Value
Sheets("Main").Cells(1, 2).Value = ""
Sheets("Main").Cells(2, 2).Value = ""
Sheets("Main").Cells(3, 2).Value = ""
l=2
Sheets("Database").Cells(l, 1).Value = i
Sheets("Database").Cells(l, 2).Value = j
Sheets("Database").Cells(l, 3).Value = k
End Sub

I want to increment value of l by 1, every time user clicks on the command button so that the new record is inserted into the next row of the Database sheet.

Answer 1

We can get the last row of a particular column by :-

dstRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1

Private Sub CommandButton1_Click()
Dim i As String
Dim j As String
Dim k As String
Dim m As String
i = Sheets("Main").Cells(1, 2).Value
j = Sheets("Main").Cells(2, 2).Value
k = Sheets("Main").Cells(3, 2).Value
m = Sheets("Main").Cells(4, 2).Value
Sheets("Main").Cells(1, 2).Value = ""
Sheets("Main").Cells(2, 2).Value = ""
Sheets("Main").Cells(3, 2).Value = ""
Sheets("Main").Cells(4, 2).Value = ""
dstRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Database").Cells(dstRw, 1).Value = i
Sheets("Database").Cells(dstRw, 2).Value = j
Sheets("Database").Cells(dstRw, 3).Value = k
Sheets("Database").Cells(dstRw, 4).Value = m
End Sub

Thanks :- Nitish Gaurav

Answer 2

Quite confused about what is you want. How is the user inputting the next row? Also at the top of your code (where you assign values to i,j and k) you are iterating the row and essentially copying a column, not a row.

I'll try to answer the remainder of your question as best I can. To make VBA know how to copy data to the next row you can do this:

Sub test()
i = "John"
j = "25"
k = "Male"
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Cells(lastRow + 1, 1) = i
Cells(lastRow + 1, 2) = j
Cells(lastRow + 1, 3) = k
End Sub

Try running that a couple of times in a row and you'll see how it works. If I misunderstood please clarify what you want.