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.

Nitish Gaurav · Accepted Answer · 2014-10-21 16:02:09Z

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

answered Oct 21, 2014 at 16:02

Nitish Gaurav

411 gold badge1 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

quantum285 · Accepted Answer · 2014-10-21 11:21:00Z

0

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.

answered Oct 21, 2014 at 11:21

quantum285

1,0322 gold badges12 silver badges23 bronze badges

2 Comments

Nitish Gaurav Over a year ago

Thanks alot...it worked. I used lastRow = Range("A" & Rows.Count).End(xlUp).Row to get the last row count and used the same as argument for Cell method.

quantum285 Over a year ago

Glad to have helped, vote my answer up as you felt it was useful :)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related