looping with Auto Filter

Smithsat34

Board Regular
Joined
Jan 20, 2014
Messages
54
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,912
Office Version
365
Platform
Windows
Do you want a new sheet for every value in col B, or just some?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,444
Office Version
365
Platform
Windows
If they are all in order like that, you could so something like:
Code:
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.
Code:
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]
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,782
Office Version
2007
Platform
Windows
Other idea. Assuming you have a header in row 1.

Code:
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

Board Regular
Joined
Jan 20, 2014
Messages
54
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,444
Office Version
365
Platform
Windows
You are welcome.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,782
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,505
Messages
5,469,013
Members
406,627
Latest member
IncandenzaH1997

This Week's Hot Topics

Top