Loop by criteria

Question

I want to loop by criteria. In some cases I have 20 criteria.

I want to filter criteria 1, copy some rows & column and make a new file and save to specific folder and the name same with the criteria.
I want to repeat it with different criteria and different name file.

Filter I want to loop

Sub Converter()
   
    Sheets("SHEET INPUT").Select
    ActiveSheet.Range("$A$2:$AQ$4652").AutoFilter Field:=22, Criteria1:="<>"
    
    ActiveSheet.Range("$A$2:$AQ$4652").AutoFilter Field:=5, Criteria1:="1"
    Range("W2:AQ2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Columns.AutoFit
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
    End With
    
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("N1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("R1").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\BJ900265\Documents\Z-Spam File\Converter Temap\PO 1.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
End Sub

To see the use of loops, this might start you off with several versions: https://stackoverflow.com/q/10380312/4961700 — Solar Mike, Feb 15 '23 at 06:41

score 0 · Answer 1 · answered Feb 15 '23 at 06:22

0

I would suggest not using excel filter but filtering in own loop while/for. I'm also newbie so this approach is based on low experience, but I can imagine it being easy to write. U can easily control number/name of criterias/WB then.

answered Feb 15 '23 at 06:22

Šimon Šťastný

13
3

This is not an answer, this is a comment. If you lack the reputation to leave comments, then please earn that reputation first some other way. – braX Feb 15 '23 at 06:51
If you give an example of a loop youi may get some votes. – Solar Mike Feb 15 '23 at 08:26
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 24 '23 at 22:34

Milad · Answer 2 · 2023-02-15T08:39:45.307

I can give you the whole idea of a loop. For the details you just have to put anything you wrote inside the loop.

You can use any kind of loop (Do-While/Do-Until/For) To this purpose. I would suggest For Loop:

First you have to count how many times your For Loop wants to be repeated, in this case it seems to be number of rows in your desired column (Criteria).To count number of rows in your desired column you can make a Range of Cells under your column header (Criteria) then Count number of this Range cells.

Let's say the column you want to count number of cells bellow is Trigger CPS 1, Then you can count the cells below it this way:

ActiveSheet.Range(Range("A3"),Range("A3").End(xlDown)).Cells.Count

So To this point you have your i value for your For Loop:

All you have to do is to put your Selecting, Copying and Pasting operations inside this loop.

Loop by criteria

2 Answers2