CODEWITHSUNDEEP
vbaexcel
Patrick Quinn
Patrick Quinn

Reputation: 23

Excel VBA Copying to Blank Row

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.

Upvotes: 2

Views: 5476

Answers (2)

martin
martin

Reputation: 2638

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).

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53137

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

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

Upvotes: 1

Related Questions