Using VBA to insert a new row in table with values

[SnuggleMuffin]

Hello,

So I have two sheets, "Insert" and "Database", in this workbook. The "Insert" sheet accepts three user inputs, "Book Title", "Author(s)", and "Year Published", which I am trying to insert into a new row at the bottom of the table in "Database", in columns A, B, and C respectively.

Right now I have the user inputs saved into variables.

Private Sub CommandButton1_Click()
    Dim strTitle As String
    Dim strAuthor As String
    Dim strDate As String
    
    strTitle = Sheets("Insert").Cells(3, 2).Value
    strAuthor = Sheets("Insert").Cells(3, 4).Value
    strDate = Sheets("Insert").Cells(3, 6).Value      
End Sub

Simple enough. What has me stumped is how to insert a new row at the bottom of the table in sheet "Database" with strTitle, strAuthor, and strDate insert into columns A, B, and C respectively in the new row.

Surprisingly I used to be a macro wizard two years ago :O. It just doesn't seem to be coming back as quickly as I had hoped!

If someone could help guide me in the right direction that'd be appreciated!

 

[My Aswer Is This]

Try this:
Assuming all your data is coming from Row(3) on sheet named "Insert"

Sub Add_My_Data()
Application.ScreenUpdating = False
'Modified 1-16-18 1:45 AM EST
Dim i As Long
Dim Lastrow As Long
Dim strTitle As String
Dim strAuthor As String
Dim strDate As String
Lastrow = Sheets("Database").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    strTitle = Sheets("Insert").Cells(3, 2).Value
    strAuthor = Sheets("Insert").Cells(3, 4).Value
    strDate = Sheets("Insert").Cells(3, 6).Value
    With Sheets("Database")
        .Cells(Lastrow, 1).Value = strTitle
        .Cells(Lastrow, 2).Value = strAuthor
        .Cells(Lastrow, 3).Value = strDate
    End With
Application.ScreenUpdating = True
End Sub

 

[SnuggleMuffin]

Try this:
Assuming all your data is coming from Row(3) on sheet named "Insert"

Sub Add_My_Data()
Application.ScreenUpdating = False
'Modified 1-16-18 1:45 AM EST
Dim i As Long
Dim Lastrow As Long
Dim strTitle As String
Dim strAuthor As String
Dim strDate As String
Lastrow = Sheets("Database").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    strTitle = Sheets("Insert").Cells(3, 2).Value
    strAuthor = Sheets("Insert").Cells(3, 4).Value
    strDate = Sheets("Insert").Cells(3, 6).Value
    With Sheets("Database")
        .Cells(Lastrow, 1).Value = strTitle
        .Cells(Lastrow, 2).Value = strAuthor
        .Cells(Lastrow, 3).Value = strDate
    End With
Application.ScreenUpdating = True
End Sub

Thanks for your response .

Could you please walk me through what exactly it is that the code is doing? I'd like to expand on my VBA knowledge, not just copy and paste

Lastrow = Sheets("Database").Cells(Rows.Count, "A").End(xlUp).Row + 1

Rows.Count, from what I understand, returns the number of rows. What does the argument "A" do? And what is .End(xlUp).Row + 1? I have seen this code before on StackOverflow, but, again, I don't really understand what the code is doing.

With Sheets("Database")
        .Cells(Lastrow, 1).Value = strTitle
        .Cells(Lastrow, 2).Value = strAuthor
        .Cells(Lastrow, 3).Value = strDate
    End With

Is this a loop or a easier way of writing the same thing multiple times?

 

[My Aswer Is This]

Lastrow = Sheets("Database").Cells(Rows.Count, "A").End(xlUp).Row + 1

Lastrow means the Lastrow in Column ("A") sheet named "Database"

This tells me the lastrow with data. So lastrow is next row to enter data in would be Lastrow +1
This way we do not over write previous entered data.

With Sheets("Database")
        .Cells(Lastrow, 1).Value = strTitle
        .Cells(Lastrow, 2).Value = strAuthor
        .Cells(Lastrow, 3).Value = strDate
    End With

So this means Sheets("Database")
lastrow column (1)
lastrow column(2)
Lastrow Column (3)


This is not a loop.

You said in your post we were only doing one at a time.
Always getting data from Row(3)
See here is what you posted:

strTitle = Sheets("Insert").Cells([COLOR=#ff0000]3[/COLOR], 2).Value
    strAuthor = Sheets("Insert").Cells([COLOR=#ff0000]3[/COLOR], 4).Value
    strDate = Sheets("Insert").Cells([COLOR=#ff0000]3[/COLOR], 6).Value

Is the code I provided doing what you wanted?