How can i separate the record according to the Gender wise?

[prince]

Hello sir, I have stored all record on my first sheet. Now i want to separate the record of Male & Female on separate sheets. Will you please help me to how can it possible.
Thanks & regards,
Prince

[HansV]

Here is a macro that you can use:

Code: Select all

Sub SplitData()
    ' Create a criteria range
    Sheet1.Range("E1") = "Gender"
    Sheet1.Range("E2") = "M"
    ' Use Advanced Filter to copy the filtered data
    Sheet1.Range("A1").CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheet1.Range("E1:E2"), _
        CopyToRange:=Worksheets("Male").Range("A1:B1")
    ' Change the criteria
    Sheet1.Range("E2") = "F"
    ' Copy the filtered data
    Sheet1.Range("A1").CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheet1.Range("E1:E2"), _
        CopyToRange:=Worksheets("Female").Range("A1:B1")
    ' Clear the criteria range
    Sheet1.Range("E1:E2").Clear
End Sub

[prince]

Thank you Mr. Hansv, its working perfectly. Now m trying this macro code with attached file Boys and Girls sheet ( Marked with red color). But unfortunately i'm not able to modify your code.
Kindly suggest me where the code will be modified.
With regards,
Prince

[HansV]

The layout of your "All Record" sheet is not suitable for filtering, since the row with field names ("Sr. No.", "Roll-No" etc.) consists of merged cells. The field name row must be a single row immediately above the data.

Since you now have data in E1 and E2, we can't use these cells as criteria range of course. We must use other cells.

The field names in the Girls and Boys sheets are now on row 3, not on row 1 as before.

You had switched the code for boys and girls in the code (I assume that F = female and M = male).

I have attached the workbook with the modified code.

Warning: if you change the layout again, the code won't work correctly anymore.

XI-New EXP.xlsm

[prince]

Thank you mr. Hansv

[prince]

Mr. Hansv , Kindly check the above attached file that you have attached. In this file boys worksheet providing accurate result but on Girls sheet showing the combine record of boys and girls.
Thanks & regards,
prince

[Rudi]

In the girls filter, change the line:

Code: Select all

CriteriaRange:=.Range("E1:E2"), _

to this line:

Code: Select all

CriteriaRange:=.Range("N1:N2"), _

[HansV]

Thanks, Rudi!

Prince, my apologies, I forgot to change the criteria range for girls. Rudi has explained how to correct it.

Also, please note that the macro is now in a standard module. Code such as this does not belong in a worksheet module.

[prince]

Thank you Mr. Hans & Mr. Rudi , Today i have almost completed my task & its credit goes to both of you. Sir, above code is working very well . Now i want to attached this code on my actual worksheet. Is this work on this sheet or not? I have not changed the basic structure of this sheet.
Thanks & Regard,
Prince

[Rudi]

Try this...

XI-New.xlsm

[prince]

Will you please tell me why in SplitData macro code is not working on the worksheet in attached file. Where is the error in file ?
Regards,
Prince

[HansV]

The code uses the Advanced Filter feature of Excel. This feature requires that the field names at the top of the data ranges on the Boys and Girls sheets are EXACTLY the same as those on the All Record sheet.
On the All Record sheet, the field name in column E is "Name of Students", but the corresponding field name on the Boys and Girls sheets is "Student's Name". Since they are not the same, the code won't work.
You must either change the value of E5 on the All Record sheet to Student's Name, or change the value of D3 on the Boys and Girls sheets to Name of Students.

[prince]

Thanks Mr. HansV , It's working now.
Thanks a lot Sir.
Regards,
Prince

[prince]

Hello Sir, In this attached file macro code of HideRow is functioning very well but why its showing the error message too. Kindly guide me where is the problem.
Thanks & regards
Prince

[Rudi]

When the original code was developed we didn't realize you would be using formulas in the cells. The code was testing for empty cells, but since you use formulas, the cells, even though they return no value, are not empty. Adding a second condition into the formula should prevent the error from happening. Maybe there is a better way of testing this, but try the code below which should prevent the error now...

Code: Select all

Sub HideRows()
Dim shS As Worksheet
Dim shD As Worksheet
Dim rgS As Range
Dim rgD As Range
Dim rgC As Range

    Set shS = Worksheets("Combined List")
    Set shD = Worksheets("Sheet2")
    Set rgS = shS.Range("A4", shS.Cells(Rows.Count, "A").End(xlUp)).Offset(0, 5)
    Application.ScreenUpdating = False
    shD.Rows.Hidden = False
    For Each rgC In rgS.Cells
        If Len(rgC.Offset(0, -2).Value) <> 0 Then
            If rgC.Value = "" Then
                Set rgD = shD.Cells.Find(What:=rgC.Offset(0, -2).Value, LookIn:=xlValues, LookAt:=xlPart)
                If Not rgD Is Nothing Then
                    Range(rgD.Offset(-6), rgD.Offset(20)).EntireRow.Hidden = True
                End If
            End If
        End If
    Next rgC
    Application.ScreenUpdating = True
End Sub

[prince]

Mr. Rudi, Its not working.

[Rudi]

Sorry...my mistake.
I forgot to remove a statement rgS.Select in the macro above as I was testing it.

I removed it now in the code above, so reselect the above macro, copy it to your workbook and try it again.

[HansV]

And to be on the safe side, the line

Code: Select all

                    Range(rgD.Offset(-6), rgD.Offset(20)).EntireRow.Hidden = True

should be

Code: Select all

                    shD.Range(rgD.Offset(-6), rgD.Offset(20)).EntireRow.Hidden = True

[Rudi]

TX Hans

[prince]

Thank You Sir, its working.
Thanks a lot Sir.