Excel VBA Copying to Blank Row

Question

Am having some difficulty with a VBA subroutine that takes information entered on the first sheet of an Excel workbook - "Entry", and pastes it into a separate sheet inside the same workbook - "Database". What I need is for the data to be added to the next empty row of the database.

I appreciate that this is quite a common question, but I've been looking on Google for the best part of this morning and can't quite get it! The code I've managed so far will copy the data, but will always overwrite the first row:

Sub CopyDataToDatabase()

Application.ScreenUpdating = False

Dim r As Range
Set r = Sheets("Entry").Range("B6:M6")
For Each cell In r
    If IsEmpty(cell) Then
        MsgBox ("Error - all boxes must be filled in!")
        Cancel = True
        Exit Sub
        Exit For
    End If
Next

Dim NextRow As Range
Set NextRow = Sheets("Database").Range("A2:L2").End(xlUp).Offset(1, 0)
'  Set NextRow = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Entry").Range("B6:M6").Copy
NextRow.PasteSpecial (xlValues)

MsgBox ("Data added successfully!")

Sheets("Entry").Range("B6:M6").ClearContents

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

The comment for Set NextRow is the other method I tried, that didn't work (error was 'Object required').

Any help gratefully received, more so if you could explain it - as I'm a bit new to VBA!

Thanks in advance.

I would recommend having a look at this link http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba To find the last cell in Col A use the method specified by @ChrisNeilson below. But if there is a possibility that Col A Last cell may not be the same as Col B - Col L then use the method `.Find` to find the last cell and then use that to create you range. — Siddharth Rout, Apr 17 '13 at 09:47

score 1 · Accepted Answer · answered Apr 17 '13 at 09:40

1

Your commented out line is almost the right method. Try this

Set NextRow = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Offset(1,0)

answered Apr 17 '13 at 09:40

chris neilsen

52,446
10
84
123

+ 1 This should work if all the columns(A till L) have the same lastrow. – Siddharth Rout Apr 17 '13 at 09:49
Worked perfectly - thank you for your help. Yes, fortunately all the data values have to be filled in, so differing column last rows aren't an issue! – Patrick Quinn Apr 17 '13 at 09:57

martin · Answer 2 · 2013-04-17T09:43:43.540

There is Sheet property UsedRange, but in my experience it is prone to errors. The way I always do it is I iterate over rows to find the first empty one.

Dim iRow as integer
iRow = 1
Dim ws as Worksheet
Set ws = Worksheets("Database")
Do While ws.Cells(iRow, 1) <> ""
    iRow = iRow + 1
Loop

In general in writing VBA code I try to avoid emulating what I do with Excel as a user (for example using Selection object or emulating keypresses).

Excel VBA Copying to Blank Row

2 Answers2

Linked