If Cell Is Blank, Copy Row to Other Sheet?

[avisser]

Hi all, I hope you're doing great! I've got a sheet for each month of tenant info for a rental company. I'm trying to improve workflow by creating an "Urgent" sheet of people that haven't submitted a needed form, pulling from the other 12 sheets. This is marked in a column, and blank cells in this column need to have their corresponding rows copied to the "Urgent" sheet.

These rows start at '7' and the column we're checking for blanks is 'J'.

Thanks in advance, you guys are awesome!

 

[rollis13]

Have a try with my macro. I'm assuming you have headers in row 6 of the sheet with the data and that column A always has data till last used row.

Option Explicit
Sub Filter_Copy_Paste()
    Dim lr1     As Long
    Dim lr2    As Long
    With Sheets(1)                                '<- adjust sheet's name as needed
        lr1 = .Cells(Rows.Count, 1).End(xlUp).Row     'last used row in Sheet(1)
        .AutoFilterMode = False
        .Range("A6:J" & lr1).AutoFilter Field:=10, Criteria1:="=" 'filter range on blanks
        lr2 = Sheets("Urgent").Cells(Rows.Count, 1).End(xlUp).Row + 1 'first unused row in sheet "Urgent"
        .Range("A6:J" & lr1).Offset(1).SpecialCells(xlCellTypeVisible).Copy Sheets("Urgent").Range("A" & lr2) 'Copy/Paste
        .AutoFilterMode = False
    End With
End Sub

 

[avisser]

Oh man, thank you so much Rollis! My first time using Macros so please bear with me. I've gotten the run-time error '1004': AutoFilter method of Range class failed. When I click 'Debug' I get this. All I've changed is the sheet name on line 4.

The Mini-Sheet extension keeps crashing Excel, so here's a screen shot if it helps. I'm trying to pull from 12 sheets all titled "JAN-22" "FEB-22" "MAR-22" and so on through the months, into "Urgent". Column A does have data, I've just deleted some things for confidentiality.

Thank you for everything my man!

 

[Joe4]

Your calculation of the last row (the "lr1" variable) is currently using the first column (column A):

lr1 = .Cells(Rows.Count, 1).End(xlUp).Row

but it looks like you have no data in column A, so it is saying that row 6 is your last row, which is your title row, and you cannot filter a range with no data!

I think you need to use some other column to find the last row of data. It looks like column B might work, so change that row above to this:

lr1 = .Cells(Rows.Count, 2).End(xlUp).Row

 

[avisser]

Column A does have data, I've just edited the picture for confidentiality. Thank you though! Any other ideas?

 

[Joe4]

Did you try changing it column B anyway?
Did it make a difference?

Add this line after the line that calculate the last row ("lr1") and see what the Message Box returns:

MsgBox "Last Row is " & lr1

 

[rollis13]

Terribly sorry but can't replicate your issue. The only possibility could be that variable lr1 is set to zero so while doing debug (please hover with the mouse over lr1 or look in the Local Variables pane or use @Joe4's suggestion in his post #6) to see what value it takes on. But there is no reason that it is zero.

 

[avisser]

It says last row is 25 (there's some data cropped out in the photo). And that's with it set to column B. Thank you!

 

[avisser]

It says last row is 25 (there's some data cropped out in the photo). And that's with it set to column B. Thank you!

And still giving the same runtime error.

 

[avisser]

Terribly sorry but can't replicate your issue. The only possibility could be that variable lr1 is set to zero so while doing debug (please hover with the mouse over lr1 or look in the Local Variables pane or use @Joe4's suggestion in his post #6) to see what value it takes on. But there is no reason that it is zero.

Oh wow, thanks for the hover tip in debug! lr=25