Finding last row using an if statement

Question

I'm very new to VBA having only taught myself a little using youtube videos and trial and error. Please be very descriptive and do not assume anything when answering.

I have a code written that is suppose to take data submitted using a command button on sheet1 and store the information on the next blank row of sheet2. The code I have works great the first time, but it will not fill in below it the next time I use it. I get an error 1004 code at the if statement. Please help.

Private Sub CommandButton21_Click()
    Dim Location As String, EmployeeName As String, itemnumber As Long, color As String, size As String, embloc As String, secopt As String, secplace As String, embstyle As String, lettering As String

    Worksheets("sheet1").Select
    Location = Range("e7")
    EmployeeName = Range("e8")
    itemnumber = Range("e9")
    color = Range("e10")
    size = Range("e11")
    embloc = Range("e12")
    secopt = Range("e13")
    secplace = Range("e14")
    embstyle = Range("e15")
    lettering = Range("e16")
    Worksheets("sheet2").Select
    Worksheets("sheet2").Range("b3").Select
    If Worksheets("sheet2").Range("B3").Offset(1, 0) <> "" Then
        Worksheets("sheet2").Range("B3").End(x1down).Select
    End If
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = Location
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = EmployeeName
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = itemnumber
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = color
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = size
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = embloc
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = secopt
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = secplace
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = embstyle
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = lettering
    Worksheets("sheet1").Select
    Worksheets("sheet1").Range("e7:e16").ClearContents
    Worksheets("sheet1").Range("e7").Select
End Sub

It won't let me post photos on here yet, but i have a table on sheet 2 that has headings from B3 to K3.

Upload the photo in a free image hosting site (eg. http://i.imgur.com) and post the link in your question. We'll take it from there. Btw, there are existing post here that you might want to check: [Finding the lastrow](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) — L42, Jan 21 '15 at 03:38
I was watching a YouTube video and the code was hard to read. I questioned that when I typed it but guess I never thought to change it — Dunn2, Jan 23 '15 at 05:11

basodre · Answer 1 · 2015-01-21T13:41:38.450

The code could use some refinement, which is understandable considering you're admittedly a new user.

First, you don't have to Select worksheets/cells to get your desired results. In fact, doing so serves as a de facto penalty as it taxes the system. Also, in this specific case, it may be redundant to store values into variables since you aren't doing much processing with them.

If I understood your code correctly, you are copying Sheet1 cells E7:E16 and pasting them into Sheet2, starting in Column B with the next available row. If correct, try this code and let's tweak it as necessary.

Private Sub CommandButton21_Click()
    Dim ws1 as Worksheet
    Dim ws2 as Worksheet
    Dim nLastRowSheet2 as Long

    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")

    nLastRowSheet2 = ws2.cells(rows.count,1).end(xlup).row

    ws1.Range("E7:E16").Copy ws2.Range("B" & nLastRowSheet2 + 1)
    ws1.Range("E7:E16").ClearContents

End Sub

Finding last row using an if statement

1 Answers1