Move items matching certain filter criteria to another sheet

[pragnesh_50]

In sheet1(column f) i have two filter criteria X/Y.


I want all data filtered with Y criteria to another sheet2.


and all data filtered with X criteria to another sheet3.


How to do that? possible with formula or macro?


Regards,

Pragnesh

 

[NARAYANK991]

Hi Pragnesh ,


You can do this without both.


As long as you have a filter in place , once you filter your data based on criteria X , copy the resulting range to sheet3 ; only that data which meets criteria X will be copied. Do the same filtering your data on criteria Y.


Narayan

 

[pragnesh_50]

Hi Narayan,


yes ....you are right.


But if i have 14 criteria to filter it takes more time compared to macro button.


So i would be thinking of macro so that all data matching 14 criteria comes in 14 sheets.


Regards,

Pragnesh

 

[NARAYANK991]

Hi Pragnesh ,


How will you specify the criteria then ?


If you have 14 criteria , say from criteria A through criteria N , you wish to apply them one at a time , copy the resulting data to one sheet , remove criteria A and apply criteria B , copy the resulting data to a second sheet , and so on.


Narayan

 

[pragnesh_50]

Hi Narayan,


I have data base of around 30 country.


In which various information regarding products, Brand, owner and other information is given for each country.


I am filtering on each country and copying and pasting it in new sheet for each country on daily basis.


I want to save my time because part of the day finished in compiling the files only.


Is this can done with pivot table something? which reduce my time.


Regards,

Pragnesh

 

[Hui]

Have a read of:

http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/

That will do mostly what you want with minor modification

 

[pragnesh_50]

Special Thanks to Hui and Narayan,


most awesome!


My problem is resolved.....

 

[pragnesh_50]

Hi Hui/Narayan,


I got the macro from the above link.


I need following updation in that macro.

1. Output should come in the new sheet instead on new Book.


Sub breakMyList()

' This macro takes values in the range myList

' and breaks it in to multiple lists

' and saves them to separate files.


Dim cell As Range

Dim curPath As String


curPath = ActiveWorkbook.Path & ""


Application.ScreenUpdating = False

Application.DisplayAlerts = False


For Each cell In Range("lstSalesman")

[valSalesman] = cell.Value

On Error Resume Next

Range("myList").AdvancedFilter Action:=xlFilterCopy, _

criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False

Range(Range("Extract"), Range("Extract").End(xlDown)).Copy

Workbooks.Add

ActiveSheet.Paste

ActiveWorkbook.SaveAs Filename:=curPath & cell.Value & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _

FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveWindow.Close

Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents

Next cell


Application.ScreenUpdating = True

Application.DisplayAlerts = True


End Sub


Could you update accordingly? as i am not conversant with VBA.


Regards,

Pragnesh