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

 

[MARK858]

Maybe something like....

Sub doriannjeshi()
    Dim actSht As Worksheet
    
    Application.ScreenUpdating = False
    Set actSht = Sheets("Sheet1")

    Worksheets.Add(after:=Sheets(Sheets.Count)).Name = "Male"
    Worksheets.Add(after:=Sheets(Sheets.Count)).Name = "Female"

    With actSht.Range("A1:F" & actSht.Range("A" & Rows.Count).End(xlUp).Row)
    
        .AutoFilter 5, "Male"
        
        On Error Resume Next
        With .SpecialCells(xlCellTypeVisible)
            .Copy Sheets("Male").Range("A1")
        End With
        On Error GoTo 0
        
        actSht.ShowAllData
        
    End With
    
    With actSht.Range("A1:F" & actSht.Range("A" & Rows.Count).End(xlUp).Row)
    
        .AutoFilter 5, "Female"
        
        On Error Resume Next
        With .SpecialCells(xlCellTypeVisible)
            .Copy Sheets("Female").Range("A1")
        End With
        On Error GoTo 0
        
       .AutoFilter
       actSht.Activate
    End With
    
    Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

A slightly different approach, assuming sheets 'Male' and 'Female' do not already exist in the workbook.

Sub MaleFemale()
  Application.ScreenUpdating = False
  With Sheets("Sheet1") '<- Change sheet name if required
    .Copy After:=Sheets(.Index)
    With ActiveSheet
      .Name = "Female"
      With .UsedRange
        .AutoFilter Field:=5, Criteria1:="<>Female"
        .Offset(1).EntireRow.Delete
        .AutoFilter
      End With
    End With
    .Copy After:=Sheets(.Index)
    With ActiveSheet
      .Name = "Male"
      With .UsedRange
        .AutoFilter Field:=5, Criteria1:="<>Male"
        .Offset(1).EntireRow.Delete
        .AutoFilter
      End With
    End With
    .Activate
  End With
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

.. or a more compact form

Sub MaleFemale_v2()
  Dim sht As Variant
  
  Application.ScreenUpdating = False
  With Sheets("Sheet1") '<- Change sheet name if required
    For Each sht In Split("Female|Male", "|")
      .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
    .Activate
  End With
  Application.ScreenUpdating = True
End Sub

 

[MARK858]

Went with Sheet1 as that is what XL2BB states it is

 

[Peter_SSs]

Went with Sheet1 as that is what XL2BB states it is

Ah, yes, I had missed that.

 

[doriannjeshi]

.. or a more compact form

Sub MaleFemale_v2()
  Dim sht As Variant
 
  Application.ScreenUpdating = False
  With Sheets("Sheet1") '<- Change sheet name if required
    For Each sht In Split("Female|Male", "|")
      .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
    .Activate
  End With
  Application.ScreenUpdating = True
End Sub

Really nice, columns also autofit
Thank you!

 

[Peter_SSs]

You're welcome. Glad we could help.

 

[doriannjeshi]

HI,
Is it possible to have male and female split from the sheet together in new sheet to be named "married", and bigender in another sheet named "bigender" ?

 

[Peter_SSs]

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.