VBA Code to sort on multiple columns and printout result

[Bomino]

Hello,
I have a worksheet that I would like to sort on multiple columns and then print out the result with the title of the report on the first row.
I would like to get the worksheet "Data", in the attached file, filtered by "Input date" (column "E"),"Sex"(Column "C" )and then "Name" (Column"A"); copy the result on another worksheet"MyFilteredResult", with the title on the first row and print it out.

Any Help would be greatly appreciated.

 

[Nebu]

Hi:
A couple of questions:
1. Do the names repeat or is going to be unique? If you are filtering by name why filter for gender . I assume the same person will not be having 2 different genders.
2. Do you want to print the filtered results?

Thanks

 

[Bomino]

Hi Nebu,
1. There could be some synonyms; There could be a "Tony" male or female. that's why I would like to sort it by Sex and Names.
2.Yes I want the results printed out.
Thank you

 

[Nebu]

Hi:

I still could not figure out what you are looking for...

Say for eg: on your file, if I filter column 'GivenName' for "Andrea" I will get three records. However, in the 'InputDate' column there are three different dates, if you again want to filter based on this column you will have essentially 3 different records , hence I presume 3 print outs (Correct me if I am wrong) . If that is the case why filter at all, can't you just loop through your data and print each record.

Also explain me the significance of your second tab "MyFilterResult", what is the difference between Data tab and MyFilterResult ?

Thanks

 

[Bomino]

Hi:

I still could not figure out what you are looking for...

Say for eg: on your file, if I filter column 'GivenName' for "Andrea" I will get three records. However, in the 'InputDate' column there are three different dates, if you again want to filter based on this column you will have essentially 3 different records , hence I presume 3 print outs (Correct me if I am wrong) . If that is the case why filter at all, can't you just loop through your data and print each record.

Also explain me the significance of your second tab "MyFilterResult", what is the difference between Data tab and MyFilterResult ?

Thanks

I am sorry for mixing up the terms "Filter " and "Sort".
I just wanted the data to be sorted by Input date, Sex and then Name; and then print out the results using VBA
I've edited the data to make it more understandable. The results should look like what I have in the tab"MySortedResult" in attached file.

I hope it make sense now

 

[Nebu]

Hi:

Here is the code

Sub test()

Application.ScreenUpdating = False

Dim rng As Range
Dim H&

If Me.AutoFilterMode Then
    Cells.AutoFilter
End If

Set rng = Range("A1", Range("A1").Offset(0, WorksheetFunction.CountA(Range("A1:E1")) - 1).Address)
H = WorksheetFunction.CountA(Range("A:A")) - 1

Range(rng.Address, rng.Offset(H, 0).Address).Sort Key1:=Range("E1"), Order1:=xlAscending, Key2:=Range( _
        "C1"), Order2:=xlAscending, Key3:=Range("B1"), Order3:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase _
        :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

Set rng = Nothing
Cells.AutoFilter

Sheet3.Range("A2:E" & Sheet3.Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
Me.Range("A1:E" & H + 1).Copy
Sheet3.Range("A2").PasteSpecial
Application.CutCopyMode = False
H = 0
Sheet3.PageSetup.PrintArea = Sheet3.Range("A1:E" & Sheet3.Cells(Rows.Count, "A").End(xlUp).Row).Address
Sheet3.Range("A1:E" & Sheet3.Cells(Rows.Count, "A").End(xlUp).Row).PrintPreview
Application.ScreenUpdating = True

End Sub

Note replace .PrintPreview with .PrintOut in your code to get the print outs.

Thanks

 

[Bomino]

Nebu,
It worked like a charm. Thank you so much for your help and patience. I really appreciated it.