仅当第一列不为空白(数据量各不相同)时，才将数据复制到另一张纸的第一空白行中 [英] Copy data to first blank row in another sheet, only if first column is not blank (amount of data varies)

问题描述

我希望能够按下一个按钮，将部分数据从工作表数据输入"复制到另一工作表数据库"的第一空白行.

I want to be able to press a button to copy some data across from sheet "Data Entry" to the first blank row in another sheet "Database".

但是，如果第一列为空白，则我不希望复制该行数据.另外，有时数据输入"工作表可能有4行数据，而有时却可能有5、6、7或8.

However, if the first column is blank, I don't want that row of data to be copied. Also, sometimes the "Data Entry" sheet may have 4 rows of data, whilst sometimes it may have 5, 6, 7 or 8.

我已经在下面附上了屏幕截图.

I've attached screenshots below.

到目前为止，我正在使用的代码没有给出任何错误，但是似乎也没有发生任何事情.

The code I'm using so far is not giving any error, but nothing seems to be happening, either.

Private Sub CommandButton1_Click()

    Dim cl As Range    
    For Each cl In Sheet2.Range("A8:A23")

        If Not IsEmpty(ActiveCell.Value) Then

            Range("A" & ActiveCell.Row & ":R" & ActiveCell.Row).Select
            Selection.Copy
            Sheets("Database").Select
            ActiveCell.End(xlDown).Offset(1, 0).Select
            ActiveSheet.Paste

        End If    
    Next cl
End Sub

推荐答案

您当前的代码一直在引用 ActiveCell (在第一次迭代后，如果有那么远的话，数据库"工作表上的单元格！)，而不是工作表2的A8:A23范围内的单元格.

Your current code is constantly referring to ActiveCell (which, after the first iteration [if it ever got that far], is a cell on the "Database" sheet!), not to the cells in range A8:A23 of Sheet2.

重构的代码可能是:

Private Sub CommandButton1_Click()
    Dim cl As Range

    For Each cl In Sheet2.Range("A8:A23")
        If Not IsEmpty(cl.Value) Then
            With Worksheets("Database") ' to make it easier to refer to the sheet
                'Find last cell in column A, 
                ' go to the row below,
                ' extend the range to be 18 columns wide,
                ' set values to be values on Sheet2
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 18).Value = cl.Resize(1, 18).Value
            End With
        End If
    Next
End Sub

这篇关于仅当第一列不为空白(数据量各不相同)时，才将数据复制到另一张纸的第一空白行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！