宏，将最后5列中任何一个空单元格中的一行复制到其他表中

As stated above, I'm trying to write a program that looks at the last 5 columns of every row in Sheet2 and copies that row if any of the cells in those last 5 columns are empty, then pastes the row into a new sheet Sheet3. I've been searching and found some helpful links but I keep getting an "Object required" error. I have a feeling I forgot something simple but I'm not getting anywhere. This is my 2nd day attempting macros, I appreciate any help!

如上所述，我正在尝试编写一个程序，它查看Sheet2中每一行的最后5列，并复制这一行，如果最后5列中的任何一个单元都是空的，则将该行粘贴到新表Sheet3中。我一直在搜索并找到一些有用的链接，但是我不断地得到一个“需要对象”的错误。我有一种感觉，我忘记了一些简单的事情，但我什么都没做。这是我第2天尝试宏，我感谢任何帮助!

Sub missingDataCopy()


Dim startColumn As Integer
Dim startRow As Integer

Dim totalRows As Integer
Dim totalColumns As Integer

Dim currentColumn As Integer
Dim currentRow As Integer

Dim shouldCopyRow As Boolean

Dim j As Long

startColumn = 7
totalColumns = 11
startRow = 1
j = 1
totalRows = Sheet2.Cells(Rows.Count, startColumn).End(xlUp).Row


For currentRow = totalRows To startRow Step -1
    shouldCopyRow = False


    For currentColumn = startColumn To totalColumns
        If IsEmpty(Sheet2.Cells(currentRow, currentColumn)) Then
            shouldCopyRow = True
            Exit For
        End If
    Next

    If shouldCopyRow Then
        Sheet2.Cells(currentRow, currentColumn).EntireRow.Copy Destination:=Worksheets("Sheet3").Range("A" & j)
        j = j + 1
    End If

1 个解决方案

#1

The error you are getting indicates that there is not Sheet in your Excel file with the CodeName Sheet2. Have a look at the following illustration to see the distinction between the Name of a sheet (which you see in the tab) and the CodeName:

您正在获取的错误表明，在您的Excel文件中没有带有CodeName Sheet2的表。看看下面的插图，看看表名(在选项卡中可以看到)和代号之间的区别:

If you wish to code with the CodeName then you must change it in the VBE as shown above. Alternatively you can also use the Name of the sheet. But then your line should read:

如果您希望使用代码名进行编码，那么您必须在VBE中更改它，如上面所示。您也可以使用表的名称。但是你的台词应该是:

totalRows = ThisWorkbook.Worksheets("Sheet1").Cells(ThisWorkbook.Worksheets("Sheet1").Rows.Count, startColumn).End(xlUp).Row

_{Because the name of the sheet in the above example is Sheet1.}

因为上面示例中的表的名称是Sheet1。

#1