Help with looping macro

[csshot2]

Below is a piece of the code that I am using. I am trying to run thru a list of cells and use each one as the criteria for autofilter. The first one is in "N7". The next would be in "N8". How can I change this code to loop thru the entire code for each cell stopping when the next cell in column N is blank???



Selection.AutoFilter Field:=1, Criteria1:=Range("N7")
Range("A1:L1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add

 

[Jonmo1]

LR = Cells(Rows.count,"N").End(xlup).row
For i = 7 to LR
Selection.AutoFilter Field:=1, Criteria1:=Range("N" & i)
rest of code

next i

 

[Norie]

Is this code's purpose to seperate data out into new workbooks based on certain criteria?

If it is it might be better to use advanced filter.

Here's an example.

Sub DistributeRowsToNewWBS()
Dim wbNew As Workbook
Dim wsData As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRow As Long
    
    Set wsData = Worksheets("Master (2)")
    Set wsCrit = Worksheets.Add
    
    LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row
    
    wsData.Range("A1:A" & LastRow).AdvancedFilter action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    Set rngCrit = wsCrit.Range("A2")
    While rngCrit.Value <> ""
        Set wsNew = Worksheets.Add
        wsData.Range("A1:E" & LastRow).AdvancedFilter action:=xlFilterCopy, CriteriaRange:=rngCrit.Offset(-1).Resize(2), CopyToRange:=wsNew.Range("A1"), Unique:=True
        wsNew.Name = rngCrit
        wsNew.Copy
        Set wbNew = ActiveWorkbook
        wbNew.SaveAs ThisWorkbook.Path & "\" & rngCrit
        wbNew.Close SaveChanges:=True
        Application.DisplayAlerts = False
        wsNew.Delete
        rngCrit.EntireRow.Delete
        Set rngCrit = wsCrit.Range("A2")
    Wend
    
    wsCrit.Delete
    Application.DisplayAlerts = True
End Sub

 

[csshot2]

Thanks!!!!!

I was able to get it to work using the first suggestion!!!! Thanks so much!! This board is a lifesaver!!!!