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]

From your screenshot it looks like your using a Table, have a try on dummyfile to get rid of it: Right-click the table, then in the shortcut menu, click Table > Convert Table to Range; try the macro again.
Elsewise, maybe even better: Right-click the table, then in the shortcut menu, click Pull Data From Table/Range (or whatever it's translated in your language) and check the data in column J for issues. You should find null for blank cells.
I'm sorry but I'm really lost (need to manage that sheet) and now it's very late here.

 

[avisser]

From your screenshot it looks like your using a Table, have a try on dummyfile to get rid of it: Right-click the table, then in the shortcut menu, click Table > Convert Table to Range; try the macro again.
Elsewise, maybe even better: Right-click the table, then in the shortcut menu, click Pull Data From Table/Range (or whatever it's translated in your language) and check the data in column J for issues. You should find null for blank cells.
I'm sorry but I'm really lost (need to manage that sheet) and now it's very late here.

BOOM! You are awesome! Now it's only copying the row up to J, rather than the entire row.
Here's what copied into the "Urgent" tab, besides the header which I pasted.

 

[rollis13]

Now it's only copying the row up to J, rather than the entire row.

Is this what you need or do you need the entire row ?
Do you need the Table ? or would a simple list be okay ?

Follow on: eventually you can add a loop to the macro to elaborate all sheets (maybe an array of names or by excluding those you don't need).

 

[avisser]

Is this what you need or do you need the entire row ?
Do you need the Table ? or would a simple list be okay ?

Follow on: eventually you can add a loop to the macro to elaborate all sheets (maybe an array of names or by excluding those you don't need).

I need to copy either the entire row or the row out to column "R".
For copying from all 12 sheets, can I just add more sheet names between the parenthesis, like this on line 4 of your Macro?

 

[rollis13]

No, that can't be done; did you try the macro .
This will do what you need:

Option Explicit
Sub Filter_Copy_Paste_Multi()
    Dim lr1    As Long
    Dim lr2    As Long
    Dim ShtNames As Variant
    Dim sht    As Variant
    Sheets("Urgent").Activate
    ShtNames = Array("JAN-22", "FEB-22", "MAR-22") 'and so on
    For Each sht In ShtNames                      'loop all sheets in ShtNames
        With Sheets(sht)
            lr1 = .Cells(Rows.Count, 1).End(xlUp).Row 'last used row in sht
            .AutoFilterMode = False
            .Range("A6:J" & lr1).AutoFilter Field:=10, Criteria1:="=" 'filter range on blanks
            lr2 = Cells(Rows.Count, 1).End(xlUp).Row + 1 'first unused row in sheet "Urgent"
            .Range("A6:R" & lr1).Offset(1).SpecialCells(xlCellTypeVisible).Copy Range("A" & lr2) 'Copy/Paste columns A to R
            .AutoFilterMode = False
        End With
    Next sht
End Sub

 

[avisser]

Perfect!! Wow You are awesome Rollis, thank you so much! Seriously saving the day on this project from work. This is for non profit homeless housing and this will save the team a lot of time to help more people. Have a great rest of your week man!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.

To avoid having to change the array when you change year you could use this other version. It will exclude hidden sheets and other sheets:

Option Explicit
Sub Filter_Copy_Paste_Multi_2()
    Dim ShtNames As Variant
    Dim sht    As Variant
    Sheets("Urgent").Activate
    For Each sht In Worksheets                    'loop all sheets
        If sht.Visible = xlSheetVisible Then      'exclude hidden
            If sht.Name <> "Urgent" Then 'And sht.Name <> "OtherSheets" Then 'exclude these sheets
                With Sheets(sht.Name)
                    .AutoFilterMode = False
                    .Range("J6:J" & .Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=1, Criteria1:="=" 'filter on blanks in column J
                    .Range("A6:R" & .Cells(Rows.Count, 1).End(xlUp).Row).Offset(1).SpecialCells(xlCellTypeVisible).Copy _
                            Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1) 'Copy/Paste columns A to R
                    .AutoFilterMode = False
                End With
            End If
        End If
    Next sht
End Sub

 

[avisser]

Oh sweet, great man thanks! I'll for sure use that instead.