CODEWITHSUNDEEP
excelvba
Condav
Condav

Reputation: 27

Macro to copy a row with an empty cell in any of the last 5 columns to other sheet

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!

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

Upvotes: 1

Views: 99

Answers (1)

Ralph
Ralph

Reputation: 9444

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:

enter image description here

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:

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.

Upvotes: 0

Related Questions