VBA:copy rows based on criteria to a new sheet/file.

[lakersbg]

Dear Excel pros,
Unfortunately I don't know much about the VBA language so I'll appreciate it if you could help me on the following macro:
Each month I get two files with data which I have to reconcile (find for each customer account (let's say each unique value in column A) the rows that are missing in one of the two files. So, I want to do a macro which would help me, once I've put the data into one sheet and sorted on Column A, to copy the rows containing each unique value in A (each customer) into a new sheet/file. After that I can easily delete the duplicate rows and see what is missing from one of the files.
I found a macro that more or less suits me, but I need to make it repeat itself for each unique value in Column A (or from a list of values if it will be easier).
Here is the macros that I found, you can modify it to suite my purpose. Big thank you in advance!
Best Regards,
Lakersbg

Sub Extract_Data()
'this macro assumes that your first row of data is a header row.
'will copy a row from one worksheet, to another blank workbook
'IF there is a 0 in column N
'Variables used by the macro
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String

'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Select Range
'(note you can change this to meet your requirements)
Range("A1:AS3000").Select
'Apply Autofilter
Selection.AutoFilter
FilterCriteria = Range("Sheet2!A1").Value
'NOTE - this filter is on column A (field:=1), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=1, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy
'Open a new file
Workbooks.Add Template:="Workbook"
'Get this file's name
NewFileName = ActiveWorkbook.Name
'Make sure you are in cell A1
Range("A1").Select
'Paste the copied cells
ActiveSheet.Paste
'Clear the clipboard contents
Application.CutCopyMode = False
'Go back to the original file
Workbooks(CurrentFileName).Activate
'Clear the autofilter
Selection.AutoFilter field:=1
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Re: VBA: autofilter criteria

Try this script.
Run this script from the data sheet
The script assumes you want the Country sheets added to the workbook
The script assumes your two sheets are named:
"Data"
"Info"

If this is not correct modify the names where indicated in red:

Sub FilterMini()
Dim i As Long
Dim Lastrow As Long
Dim SheetName As String
Dim Sex As String
Dim Country As String
Dim One As String
Dim Two As String
One = "Data" [COLOR=#ff0000]'Modify name here if needed[/COLOR]
Two = "Info" [COLOR=#ff0000]'Modify name here if needed[/COLOR]
Lastrow = Sheets(Two).Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(One).Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To Lastrow
SheetName = Sheets(Two).Cells(i, "A").Value
Country = Sheets(Two).Cells(i, "B").Value
Sex = Sheets(Two).Cells(i, "C").Value
Sheets.Add(After:=Sheets(Sheets.Count)).Name = SheetName
With Sheets(One).Range("A1:G" & Lastrowa)
.AutoFilter Field:=7, Criteria1:=Country, Operator:=xlFilterValues
.AutoFilter Field:=5, Criteria1:=Sex, Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Copy Worksheets(SheetName).Range("A1")
End With
Next
Sheets(One).AutoFilterMode = False
End Sub

 

[My Aswer Is This]

Now I notice you have made another posting
Your image in post 119 and 120 are not the same

In post 119 your showing country in column "G"

And sex in column "E"

 

[Kyotaki]

Awesome code! I have just a problem, my third criteria is just Italy but the output is empty because the macro filter for empty sex cells. My necessity is that when the criteria is empty that column shouldn't be taken into account to filter the data.

I give an example:
If in my criteria the country is Italy and sex empty than I need all the entry from Italy (both Male and Female)

Second example:
If in my criteria the country is empty and sex is male, I want male people from all the countries.

How can I implement it?

 

[My Aswer Is This]

This should work for your first if statement about sex= nothing

Sub FilterMini()
'Modified 3-18-17 5:28 PM EST
Dim i As Long
Dim Lastrow As Long
Dim SheetName As String
Dim Sex As String
Dim Country As String
Dim One As String
Dim Two As String
One = "Data" 'Modify name here if needed
Two = "Info" 'Modify name here if needed
Lastrow = Sheets(Two).Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(One).Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To Lastrow
SheetName = Sheets(Two).Cells(i, "A").Value
Country = Sheets(Two).Cells(i, "B").Value
Sex = Sheets(Two).Cells(i, "C").Value
Sheets.Add(After:=Sheets(Sheets.Count)).Name = SheetName
With Sheets(One).Range("A1:G" & Lastrowa)
.AutoFilter Field:=7, Criteria1:=Country, Operator:=xlFilterValues
If Len(Sex) > 0 Then .AutoFilter Field:=5, Criteria1:=Sex, Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Copy Worksheets(SheetName).Range("A1")
End With
Next
Sheets(One).AutoFilterMode = False
End Sub

I am concerned about your second if statement wondering how many more if statements you may want.

 

[Kyotaki]

Unfortunately it does not work properly, the macro now provides items from Italy with the last sex criteria available in the criteria list,in this case the one from Germany which is male, so I get just Italy-Male data. If I change the filter criteria in Germany to Female I get Italy-Female data.

 

[My Aswer Is This]

Yes I wondered later if I tested that properly. I will have to work on it some more.

Unfortunately it does not work properly, the macro now provides items from Italy with the last sex criteria available in the criteria list,in this case the one from Germany which is male, so I get just Italy-Male data. If I change the filter criteria in Germany to Female I get Italy-Female data.

 

[My Aswer Is This]

I just went back and tested the script and it works for me. You must be exaxctly correct entering values.
I suggest using data validation for enter Female and Male and contry names. And your sheet nust be set up as shown in image shown in post #119

Unfortunately it does not work properly, the macro now provides items from Italy with the last sex criteria available in the criteria list,in this case the one from Germany which is male, so I get just Italy-Male data. If I change the filter criteria in Germany to Female I get Italy-Female data.

 

[My Aswer Is This]

Be sure in every cell on sheet "Data" you have some value in column "A".
The script looks down Column "A" till it finds no more values.
And make sure on sheet "Info" you have nothing in the cells with no sex. Do not put something like "None"

 

[Kyotaki]

My dataset seems to match the requirements you have listed, however for Italy I still get data for just one sex.

I've uploaded it to Dropbox if you want to give a look (your code is incorporated) https://www.dropbox.com/s/txkb02py3kpvr0y/TOY_DATASAMPLE.xlsm?dl=0

May it depend on the Excel version? I have Excel 2007 home edition.

 

[My Aswer Is This]

I see the problem but have not figured out how to solve it. Will get back with you.