VBA macro with customizable autofilter

[Kyotaki]

Hello,

My Aswer Is This in another thread helped me elaborating the code below (see here https://www.mrexcel.com/forum/excel...d-criteria-new-sheet-file-14.html#post4782648). It allows me to use the autofilter setting up the conditions in a sheet instead of hardcoding them. Moreover this code adds a sheet per each category, the name and the number of the sheets are part of the customizable set-up sheet.

I need to add a feature to this macro, sometimes I want the data from two categories to be copied in the same sheet instead of two different sheets. I'd like to implement it in the same macro below.

I provide an example from the dataset attached. I need German and French males to be filtered to the same sheet and Italians to another one. How is it possible to do it?

Dataset available here: https://www.dropbox.com/s/i3f9ikcr03rrfk6/new_TOY_DATASAMPLE.xlsm?dl=0


Conditions for the filter:

Screenshot from the dataset

Sub FilterMini()
'Modified 3-20-17 4:35 AM EST
'Male not Hardcoded
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)
If Len(Country) = "0" And Sex = Sex Then
.AutoFilter Field:=7, Criteria1:="*", Operator:=xlFilterValues
.AutoFilter Field:=5, Criteria1:=Sex, Operator:=xlFilterValues
Else
If Len(Country) > 0 Then .AutoFilter Field:=7, Criteria1:=Country, Operator:=xlFilterValues
If Len(Sex) > 0 Then .AutoFilter Field:=5, Criteria1:=Sex, Operator:=xlFilterValues
End If
.SpecialCells(xlCellTypeVisible).Copy Worksheets(SheetName).Range("A1")
End With
Sheets(One).AutoFilterMode = False
Next
Sheets(One).AutoFilterMode = False
End Sub

 

[My Aswer Is This]

So after looking at the script here you have not modified the script I provided. Unless I missed something.
So tell me what's not working the way you now want.

If you create a new sheet named France/Germany then Put France/Germany in column A of sheet "Data" then I would think what you now want would work.

 

[Kyotaki]

What I need is an OR condition, if (country=France or country=germany) and sex=male, than copy paste to sheet France&Germany. It is not clear to me If I can do this changing the cells values in the setup sheet.

The final output should be just two new sheets (France&Germany, Italy) in France&Germany I need Data for both French and Germans male people.

At the moment if I put in column A two cells with the same name the macro stops working because excel cannot rename two sheets with the same name.

 

[My Aswer Is This]

But doing it this ways then were hard coding the script.
Where you said:
What I need is an OR condition, if (country=France or country=germany) and sex=male, than copy paste to sheet France&Germany. It is not clear to me If I can do this changing the cells values in the setup sheet.

In our previous scripts we were attempting to get away from hardcoding.

We had one hardcoded line of code but now were getting more hardcoding

I wonder if there is not some other plan where we could use cell value for example like we were using in Info sheet

Then you can just change cell value in info sheet every time you want a different scenario instead of hardcoding

 

[My Aswer Is This]

To keep from using hard coding lets try this

In your info sheet if you want to look for two countries do this
In column "A" Put "GermanyFrance"
In Column "B" Put "Germany"
In Column "C" Put "Male"
In column "D" put "France"

And then run this script.

This should work with what ever countries you enter or Sex you enter.

Sub FilterMini()
'Modified 3-29-17 5:551 AM EDT
'Male not Hardcoded
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
CountryTwo = Sheets(Two).Cells(i, "D").Value
Sex = Sheets(Two).Cells(i, "C").Value
Sheets.Add(After:=Sheets(Sheets.Count)).Name = SheetName
With Sheets(One).Range("A1:G" & Lastrowa)
If Len(Country) = "0" And Sex = Sex Then
.AutoFilter Field:=7, Criteria1:="*", Operator:=xlFilterValues
.AutoFilter Field:=5, Criteria1:=Sex, Operator:=xlFilterValues
Else
'New Part 3-29-17
If Len(CountryTwo) > 0 Then
.AutoFilter Field:=7, Criteria1:=Array(Country, CountryTwo), Operator:=xlFilterValues
Else
If Len(Country) > 0 Then .AutoFilter Field:=7, Criteria1:=Country, Operator:=xlFilterValues
End If
If Len(Sex) > 0 Then .AutoFilter Field:=5, Criteria1:=Sex, Operator:=xlFilterValues
End If
.SpecialCells(xlCellTypeVisible).Copy Worksheets(SheetName).Range("A1")
End With
Sheets(One).AutoFilterMode = False
Next
Sheets(One).AutoFilterMode = False
End Sub

 

[Kyotaki]

Ok I see what you have done, the problem with this strategy is that if I need to filter to the same sheet Germans, French, Italians, British, Spanish I would need to add more and more cells and also in the code I would need more if Len conditions.

Do you think that another way is possible to do it? An example I was thinking to instruct excel to analyze the sheetname column, if there is the name of a already existent sheet (eg Germany&France) than excel should activate the autofilter and paste the data starting from the first empty row.

 

[My Aswer Is This]

I'm not sure what your saying here:
if there is the name of a already existent sheet (eg Germany&France) than excel should activate the autofilter and paste the data starting from the first empty row.

Paste what data and from the first empty row. First empty cell is that what you mean and first empty cell where. You mean first empty cell in column "A" of sheet "info"


And are you now saying you may not always be wanting to delete all the country sheets which is what I assume you must have been doing before.

You can see how this project keep growing each time and more and more needs are wanted. And this is normal with almost any project.


It would be a little easier for me if I knew what the overall objective of this project is that way I may be able to think more about possible long term ways to do things. We may need to look at having you enter certain value into certain cells telling the script what to do.

We are doing that already but as more and more things are wanted we may need to think more.
See at first we were just dealing with Country and sex then it was country and both sexes and the all countries and both sexes. Now were trying to do several identified countries and both or single sexes.

It is good that you can read the scripting and understand the logic.

So maybe you could explain your current thinking now on how we should do this.
I'm not clear what your ideal was. I like helping on projects like this but then when I really do not know what the end goal is it can make it more interesting. Glad to see your always thinking of more things you may want. I know I start projects for myself and then are always thinking of new things I want to add to the project and thinking about how to do this or that.