looping with Auto Filter

[Smithsat34]

Hi board,

I recorded the following macro:

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveSheet.Range("$A$1:$Q$2975").AutoFilter Field:=2, Criteria1:="2014"
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Sheets("Bord Dec. 18").Select
Range("B1").Select
End Sub

What would be great would be if I could somehow loop through a list of values for criteria1. i.e. 2018, 2017, 2016, 2015, 2014.
If I could then define the name of each 'new' sheet with the criteria1 selection.

Any suggestions gratefully received

Regards
Smith

 

[Fluff]

Do you want a new sheet for every value in col B, or just some?

 

[Joe4]

If they are all in order like that, you could so something like:

For yr = 2014 to 2018
[COLOR=#333333]    ActiveSheet.Range("$A$1:$Q$2975").AutoFilter Field:=2, Criteria1:=yr
    ...
[/COLOR]

Or, you could store the values in an array, and iterate through it, i.e.

Dim yr as Variant
Dim i as Long
yr = Array("2014", "2015", "2016", "2017", "2018")
For i = LBound(yr) to UBound(yr)
[COLOR=#333333]    ActiveSheet.Range("$A$1:$Q$2975").AutoFilter Field:=2, Criteria1:=yr(i)
    ...[/COLOR]

 

[DanteAmor]

Other idea. Assuming you have a header in row 1.

Sub Looping_Auto_Filter()
    Dim sh As Worksheet, Ky As Variant, c As Range
    
    Set sh = ActiveSheet
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    With CreateObject("scripting.dictionary")
        For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(xlUp))
            If c.Value <> "" Then .Item(c.Value) = Empty
        Next
        For Each Ky In .Keys
            sh.Range("A1").AutoFilter 2, Ky
            Sheets.Add after:=Sheets(Sheets.Count)
            sh.AutoFilter.Range.EntireRow.Copy Range("A1")
        Next
    End With
    sh.ShowAllData
End Sub

 

[Smithsat34]

Thanks All,
I went with Joe4's solution to get me going but when I get a chance I'll have a go with Dante's as it may solve another issue that I think is coming! Once again, thanks for looking

 

[Joe4]

You are welcome.

 

[DanteAmor]

Thanks All,
I went with Joe4's solution to get me going but when I get a chance I'll have a go with Dante's as it may solve another issue that I think is coming! Once again, thanks for looking

That is the idea, that the solution serves for this problem or perhaps for some other problem of some other person.