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]

First things first.
If you get in the habit of sheet naming sometimes capitalized and other times not, you'll get errors in your code (see Post #1)

 

[Peter_SSs]

First things first.
If you get in the habit of sheet naming sometimes capitalized and other times not, you'll get errors in your code (see Post #1)

Have you posted in the wrong thread?

@Phily50
See If this would help.

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	Hdr10	Hdr11	Hdr12	Hdr13
2	Data 67	Data 35	Data 31	Data 6	Data 16	Data 10	Y	Data 80	Data 49	Data 90	Data 47	Data 64	Data 25
3	Data 13	Data 90	Data 56	Data 39	Data 5	Data 2	N	Data 57	Data 20	Data 53	Data 36	Data 18	Data 42
4	Data 16	Data 71	Data 31	Data 14	Data 38	Data 52		Data 8	Data 11	Data 84	Data 70	Data 81	Data 15
5	Data 66	Data 21	Data 99	Data 68	Data 47	Data 41	N	Data 15	Data 99	Data 89	Data 35	Data 3	Data 29
6	Data 76	Data 25	Data 81	Data 76	Data 36	Data 90	Y	Data 33	Data 20	Data 64	Data 62	Data 71	Data 46
7
Master

Formula needs to go in A2 only

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	Hdr10	Hdr11	Hdr12	Hdr13
2	Data 67	Data 35	Data 31	Data 6	Data 16	Data 10	Y	Data 80	Data 49	Data 90	Data 47	Data 64	Data 25
3	Data 76	Data 25	Data 81	Data 76	Data 36	Data 90	Y	Data 33	Data 20	Data 64	Data 62	Data 71	Data 46
4
Destroyed

Cell Formulas
Range	Formula
A2:M3	A2	=FILTER(Master!A2:M1000,Master!G2:G1000="Y","")
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	Hdr10	Hdr11	Hdr12	Hdr13
2	Data 13	Data 90	Data 56	Data 39	Data 5	Data 2	N	Data 57	Data 20	Data 53	Data 36	Data 18	Data 42
3	Data 66	Data 21	Data 99	Data 68	Data 47	Data 41	N	Data 15	Data 99	Data 89	Data 35	Data 3	Data 29
4
Retained

Cell Formulas
Range	Formula
A2:M3	A2	=FILTER(Master!A2:M1000,Master!G2:G1000="N","")
Dynamic array formulas.

 

[jolivanes]

This should do the trick.

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
Sheets("Retained").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial
  .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Hi Peter.
"Master - this contains the full data
Destroyed and Retained"
and next
"copies the entire row (columns A to M) into destroyed and retained"

No serious problem but how many Posts have you answered where Sheet names were wrong?
Not much different from where there are leading or trailing spaces.
Might as well get them used to doing it properly.

 

[Peter_SSs]

and next
"copies the entire row (columns A to M) into destroyed and retained"

OK, see what you were getting at now.

This should do the trick.

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)

I would need this to either update dynamically or through the push of a button prior to exiting.

@Phily50

and then sorting them by date,

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).

 

[jolivanes]

All the supplied code does is copy filtered data into the two sheets at the next empty cell in Column A. No sorting. If they want the two sheets cleared before pasting, that would obviously be no problem. But again, to me that falls in the educational part that they should think things thru before asking. And I also realize that most do not think that far ahead but that will have changed for the next time when they will remember to supply all the relevant information hopefully. It will always be an ongoing battle but to me it is the best way of learning.
I am probably wrong but in my opinion, people should put some effort into it themselves by trying to work things out. This has the advantage that they learn something also.
If they cannot get it to work, come back here, explain what you tried and ask for help for that problem.

 

[Peter_SSs]

All the supplied code does is copy filtered data into the two sheets at the next empty cell in Column A.

My question was have you tested it?

 

[jolivanes]

Yes I did

 

[Peter_SSs]

Yes I did

Fair enough. Wen I ran it this happened:

1. It filtered the data for "Y" (=Destroyed)
2. Copied the filtered data
3. Pasted that data on the Destroyed sheet
4. Pasted exactly the same data (ie the 'destroyed' data) on the Retained sheet (not the 'retained' data)

 

[jolivanes]

I think I see what you mean. I do have to read Post #1 again a few times to comprehend what is requested and to sort the requirements.