Copy range based on criteria in column - macro

[doriannjeshi]

Hi,
how to copy the entire data only for "male" to a new sheet named "male" , also for female. Using a macro

Book2
	A	B	C	D	E	F
1	id	first_name	last_name	email	gender	ip_address
2	1	Rikki	Pere	rpere0@alexa.com	Male	61.164.210.42
3	2	Vick	Ambrozik	vambrozik1@google.it	Male	102.222.176.131
4	3	Kali	Shelton	kshelton2@slate.com	Bigender	209.89.60.163
5	4	Lodovico	Shears	lshears3@bandcamp.com	Male	175.100.178.34
6	5	Rhoda	Gavaran	rgavaran4@alexa.com	Female	131.26.160.86
7	6	Gerri	Neale	gneale5@independent.co.uk	Male	76.95.229.131
8	7	Tod	Emmitt	temmitt6@dot.gov	Male	249.234.72.77
9	8	Killy	Louys	klouys7@networksolutions.com	Male	226.205.57.155
10	9	Johnna	Manlow	jmanlow8@freewebs.com	Female	66.160.3.172
11	10	Shandeigh	Possell	spossell9@blogspot.com	Female	54.79.95.159
12	11	Thelma	Corballis	tcorballisa@mediafire.com	Female	209.246.69.109
13	12	Christabella	McCowan	cmccowanb@skyrock.com	Female	254.29.4.81
14	13	Tristan	Stede	tstedec@weibo.com	Male	58.238.118.14
15	14	Klara	Storres	kstorresd@parallels.com	Female	103.61.128.129
16	15	Bendix	Cavnor	bcavnore@addthis.com	Polygender	115.197.228.198
17	16	Pammi	Whiles	pwhilesf@archive.org	Female	235.85.253.158
18	17	Sena	Fothergill	sfothergillg@indiatimes.com	Female	121.116.167.159
19	18	Ofelia	Sargison	osargisonh@uiuc.edu	Female	30.29.132.119
20	19	Oberon	End	oendi@dedecms.com	Male	19.176.34.160
21	20	Caresa	Kybbye	ckybbyej@a8.net	Agender	157.221.2.249
22	21	Saundra	Yelding	syeldingk@reddit.com	Male	240.178.148.42
23	22	Daisy	Parnell	dparnelll@home.pl	Female	5.136.226.91
24	23	Prue	Corran	pcorranm@istockphoto.com	Female	16.235.134.99
25	24	Gamaliel	Moysey	gmoyseyn@t-online.de	Male	56.38.94.245
26	25	Pebrook	Quinion	pquiniono@naver.com	Male	88.85.159.192
27	26	Douglass	Rossi	drossip@yahoo.co.jp	Male	20.52.3.13
28	27	Kayne	Merton	kmertonq@fotki.com	Male	228.35.108.191
29	28	Oona	Biddlestone	obiddlestoner@trellian.com	Female	25.80.120.56
30	29	Theadora	Blacktin	tblacktins@jimdo.com	Female	7.174.1.190
31	30	Phelia	Rykert	prykertt@ning.com	Female	128.142.83.253
Sheet1

result

Book2
	A	B	C	D	E	F
1	id	first_name	last_name	email	gender	ip_address
2	1	Rikki	Pere	rpere0@alexa.com	Male	61.164.210.42
3	2	Vick	Ambrozik	vambrozik1@google.it	Male	102.222.176.131
4	4	Lodovico	Shears	lshears3@bandcamp.com	Male	175.100.178.34
5	6	Gerri	Neale	gneale5@independent.co.uk	Male	76.95.229.131
6	7	Tod	Emmitt	temmitt6@dot.gov	Male	249.234.72.77
7	8	Killy	Louys	klouys7@networksolutions.com	Male	226.205.57.155
8	13	Tristan	Stede	tstedec@weibo.com	Male	58.238.118.14
9	19	Oberon	End	oendi@dedecms.com	Male	19.176.34.160
10	21	Saundra	Yelding	syeldingk@reddit.com	Male	240.178.148.42
11	24	Gamaliel	Moysey	gmoyseyn@t-online.de	Male	56.38.94.245
12	25	Pebrook	Quinion	pquiniono@naver.com	Male	88.85.159.192
13	26	Douglass	Rossi	drossip@yahoo.co.jp	Male	20.52.3.13
14	27	Kayne	Merton	kmertonq@fotki.com	Male	228.35.108.191
male

 

[doriannjeshi]

1. How do we know who is married? Surely not every male and every female is married?
   
   
2. Is this request in addition to the previous one (that is, do we still generate 'male' and 'female' sheets? Or is is a completely separate question with only two sheets generated

BTW, are those real names and real email addresses? If so I will remove them and replace with fakes.

Hi Peter,
this is bogus data , not real

Yes is the same request but this time content of "male","female" needs to be copied together to a new sheet "married" and what else is specified i.e Bigender split into their respective sheets

 

[Peter_SSs]

this is bogus data , not real

Great, thanks.

and what else is specified i.e Bigender split into their respective sheets

Not entirely clear on that.

• Bigender, Polygender, Agender all to one sheet, or
• Bigender to one sheet, Polygender to another sheet, Agender to another sheet.

Do I assume that none of the 'Married' or other gender sheets already exist?

 

[doriannjeshi]

Great, thanks.


Not entirely clear on that.

• Bigender, Polygender, Agender all to one sheet, or
• Bigender to one sheet, Polygender to another sheet, Agender to another sheet.

Do I assume that none of the 'Married' or other gender sheets already exist?

1. No seperate sheets, named after them. But only copy the content to another sheet of what I specifiy i.e Bigender ok , Polygender not copied at all if not specified
2.Yes seperately , but only those specified

the sheets do not exist but need to be created

copy - male, female both to a new sheet to be named Married
copy - what else is specified to a each a sheet

 

[Peter_SSs]

OK, try this

Sub MaleFemale_v3()
  Dim sht As Variant
  
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
    For Each sht In Split("Agender|Polygender|Bigender", "|")
      .Copy After:=Sheets(.Index)
      With ActiveSheet
        .Name = sht
        With .UsedRange
          .AutoFilter Field:=5, Criteria1:="<>" & sht
          .Offset(1).EntireRow.Delete
          .AutoFilter
        End With
      End With
    Next sht
    .Copy After:=Sheets(.Index)
    With ActiveSheet
      .Name = "Married"
      With .UsedRange
        .AutoFilter Field:=5, Criteria1:="<>Male", Operator:=xlAnd, Criteria2:="<>Female"
        .Offset(1).EntireRow.Delete
        .AutoFilter
      End With
    End With
    .Activate
  End With
  Application.ScreenUpdating = True
End Sub

 

[doriannjeshi]

OK, try this

Sub MaleFemale_v3()
  Dim sht As Variant
 
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
    For Each sht In Split("Agender|Polygender|Bigender", "|")
      .Copy After:=Sheets(.Index)
      With ActiveSheet
        .Name = sht
        With .UsedRange
          .AutoFilter Field:=5, Criteria1:="<>" & sht
          .Offset(1).EntireRow.Delete
          .AutoFilter
        End With
      End With
    Next sht
    .Copy After:=Sheets(.Index)
    With ActiveSheet
      .Name = "Married"
      With .UsedRange
        .AutoFilter Field:=5, Criteria1:="<>Male", Operator:=xlAnd, Criteria2:="<>Female"
        .Offset(1).EntireRow.Delete
        .AutoFilter
      End With
    End With
    .Activate
  End With
  Application.ScreenUpdating = True
End Sub

very nice ,
How do I add to the married sheet ?
.AutoFilter Field:=5, Criteria1:="<>Male", Operator:=xlAnd, Criteria2:="<>Female", Operator:=xlAnd, Criteria3:="<>Polygender"

 

[Peter_SSs]

How do I add to the married sheet ?

The code does that

Sub MaleFemale_v3()
  Dim sht As Variant
 
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
    For Each sht In Split("Agender|Polygender|Bigender", "|")
      .Copy After:=Sheets(.Index)
      With ActiveSheet
        .Name = sht
        With .UsedRange
          .AutoFilter Field:=5, Criteria1:="<>" & sht
          .Offset(1).EntireRow.Delete
          .AutoFilter
        End With
      End With
    Next sht
    .Copy After:=Sheets(.Index)
    With ActiveSheet
      .Name = "Married"
      With .UsedRange
        .AutoFilter Field:=5, Criteria1:="<>Male", Operator:=xlAnd, Criteria2:="<>Female"
        .Offset(1).EntireRow.Delete
        .AutoFilter
      End With
    End With
    .Activate
  End With
  Application.ScreenUpdating = True
End Sub

Here are my tabs after starting with just 'Sheet1' and running the above code.

 

[MARK858]

@Peter_SSs the OP is trying to add a third criteria

.AutoFilter Field:=5, Criteria1:="<>Male", Operator:=xlAnd, Criteria2:="<>Female", Operator:=xlAnd, Criteria3:="<>Polygender"

 

[Peter_SSs]

@Peter_SSs the OP is trying to add a third criteria

Thanks Mark. Careless on my part - I read this without the "to"

How do I add to the married sheet ?

@doriannjeshi
Sorry about misreading post 15. See if this is what you want then.

• Each gender value that you want to go to an individual sheet, list between "|" characters in the "IndividualSheets" line near the top of the code below.
• Everything else will get combined into a single "Married" sheet.

Sub Gender_Split()
  Dim sht As Variant, IndividualSheets As Variant
  
  IndividualSheets = Split("Agender|Bigender", "|") '<- Specify any idividual sheets here
  
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
  
    'This section creates the "individual" sheets
    For Each sht In IndividualSheets
      .Copy After:=Sheets(.Index)
      With ActiveSheet
        .Name = sht
        With .UsedRange
          .AutoFilter Field:=5, Criteria1:="<>" & sht
          .Offset(1).EntireRow.Delete
          .AutoFilter
        End With
      End With
    Next sht
    
    'This section creates a single sheet for all those EXCEPT the individual ones listed above.
    .Copy After:=Sheets(.Index)
    With ActiveSheet
      .Name = "Married"
      With .UsedRange
        .AutoFilter Field:=5, Criteria1:=IndividualSheets, Operator:=xlFilterValues
        .Offset(1).EntireRow.Delete
        .AutoFilter
      End With
    End With
    
    .Activate
  End With
  Application.ScreenUpdating = True
End Sub

 

[doriannjeshi]

Thanks Mark. Careless on my part - I read this without the "to"


@doriannjeshi
Sorry about misreading post 15. See if this is what you want then.

• Each gender value that you want to go to an individual sheet, list between "|" characters in the "IndividualSheets" line near the top of the code below.
• Everything else will get combined into a single "Married" sheet.

Sub Gender_Split()
  Dim sht As Variant, IndividualSheets As Variant
 
  IndividualSheets = Split("Agender|Bigender", "|") '<- Specify any idividual sheets here
 
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
 
    'This section creates the "individual" sheets
    For Each sht In IndividualSheets
      .Copy After:=Sheets(.Index)
      With ActiveSheet
        .Name = sht
        With .UsedRange
          .AutoFilter Field:=5, Criteria1:="<>" & sht
          .Offset(1).EntireRow.Delete
          .AutoFilter
        End With
      End With
    Next sht
  
    'This section creates a single sheet for all those EXCEPT the individual ones listed above.
    .Copy After:=Sheets(.Index)
    With ActiveSheet
      .Name = "Married"
      With .UsedRange
        .AutoFilter Field:=5, Criteria1:=IndividualSheets, Operator:=xlFilterValues
        .Offset(1).EntireRow.Delete
        .AutoFilter
      End With
    End With
  
    .Activate
  End With
  Application.ScreenUpdating = True
End Sub

Thank you Peter and Mark,
What about the ones that I don't want to appear in any sheet ?

 

[Peter_SSs]

Thank you Peter and Mark,
What about the ones that I don't want to appear in any sheet ?

This has not been mentioned before. Just as Married was not mentioned at the start.
You need to specify your requirements (all of them) at the beginning rather than getting an answer and then changing the requirement continually.