Excel VBA复制到空白行 [英] Excel VBA Copying to Blank Row

问题描述

Am在使用VBA子例程时遇到一些困难，该子例程需要将在Excel工作簿的第一张工作表中输入的信息-"Entry"，并将其粘贴到同一工作簿-"Database"中的单独工作表中.我需要将数据添加到数据库的下一个空行中.

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.

我很欣赏这是一个很常见的问题，但是今天上午我一直在Google上寻找最好的部分，实在无法完全理解！到目前为止，我管理的代码将复制数据，但始终会覆盖第一行:

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

设置NextRow的注释是我尝试过的另一种方法，但没有用(错误是需要对象").

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

任何帮助我们都会感激不尽，如果您能解释一下，请多提供帮助-因为我对VBA有点陌生！

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

谢谢.

推荐答案

您注释掉的行几乎是正确的方法.试试这个

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

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

这篇关于Excel VBA复制到空白行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！