Copy entire row if cell contains specific value

[Phily50]

So I have a workbook with three sheets.

Master - this contains the full data
Destroyed and Retained

Within Master there is a column (G) that has a "Y" to indicate that the file has been destroyed and an "N" to indicate that the file is retained. Currently some are blank but these will be changed as and when the documents are reviewed. There will also be further data added to the master sheet as time goes on.

The headings in all three sheets will be the same

What I am after is either a code or a formula that scans the master sheet and copies the entire row (columns A to M) into destroyed and retained dependant on column G (the Y/N). I would need this to either update dynamically or through the push of a button prior to exiting. Currently I'm manually adding them b y copy pasting and then sorting them by date, its rather laborious and repetitive!

I had a go using FILTER but couldn't work it out.

Grateful of any assistance.

 

[jolivanes]

This should make more sense.

Sub Maybe()
Application.ScreenUpdating = False
With Range("A1:M" & Cells(Rows.Count, 2).End(xlUp).Row)
  .AutoFilter 7, "Y"
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Copy
        Sheets("Destroyed").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial
            .AutoFilter 7, "N"
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Copy
    Sheets("Retained").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial
  .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Have not heard if sheets need to be cleared, which makes sense also and have not included sorting.

 

[Phily50]

OK, see what you were getting at now.


Just wondering if you have actually tested your code though? It didn't work correctly for me.

And also wondering how that code would handle the following without continually adding the same data to the other sheet(s)


@Phily50

I had overlooked this comment previously. If you could advise which column contains the date, my formulas could be adjusted so that the data was also sorted by date (if it isn't already).

Hi Peter, the formula works but with reference to which column I need it sorted by is J

*edit* is there a way to deal with blanks? currently blank cells are showing as 0

 

[Peter_SSs]

*edit* is there a way to deal with blanks? currently blank cells are showing as 0

Below is one way.
I have also included the sorting though I wasn't sure if the dates should be ascending or descending. If I have it the wrong sort order than change to 1 at the end of the formula to -1.
Provided where I have 1000 in the formula you have used a number big enough to include any amount of data you are likely to end up with everything should automatically update as you add/remove/edit data on the Master sheet.

Phily50.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Destroyed	Hdr8	Hdr9	Date	Hdr11	Hdr12	Hdr13
2	Data 67	Data 35			Data 16	Data 10	Y	Data 80	8	05-Aug-22	Data 47	Data 64	Data 25
3	Data 13	Data 90			Data 5	Data 2	N	Data 57	6	16-Jul-20	Data 36	Data 18	Data 42
4	Data 16	Data 71			Data 38	Data 52		Data 8	5	14-May-22	Data 70	Data 81	Data 15
5	Data 66	Data 21			Data 47	Data 41	N	Data 15		18-Jan-23	Data 35	Data 3	Data 29
6	Data 76	Data 25	Data 81	Data 76	Data 36	Data 90	Y	Data 33		25-Jun-22	Data 62	Data 71	Data 46
Master

Phily50.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Destroyed	Hdr8	Hdr9	Date	Hdr11	Hdr12	Hdr13
2	Data 76	Data 25	Data 81	Data 76	Data 36	Data 90	Y	Data 33		25-Jun-22	Data 62	Data 71	Data 46
3	Data 67	Data 35			Data 16	Data 10	Y	Data 80	8	05-Aug-22	Data 47	Data 64	Data 25
4
Destroyed

Cell Formulas
Range	Formula
A2:M3	A2	=LET(r,Master!A2:M1000,SORT(FILTER(IF(r="","",r),Master!G2:G1000="Y",""),10,1))
Dynamic array formulas.

Phily50.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Destroyed	Hdr8	Hdr9	Date	Hdr11	Hdr12	Hdr13
2	Data 13	Data 90			Data 5	Data 2	N	Data 57	6	16-Jul-20	Data 36	Data 18	Data 42
3	Data 66	Data 21			Data 47	Data 41	N	Data 15		18-Jan-23	Data 35	Data 3	Data 29
4
Retained

Cell Formulas
Range	Formula
A2:M3	A2	=LET(r,Master!A2:M1000,SORT(FILTER(IF(r="","",r),Master!G2:G1000="N",""),10,1))
Dynamic array formulas.