refine advanced filter

[raccoon588]

Is there a better, more efficient way to complete all these advanced filters?

Sub Sort()


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l4:M5"), CopyToRange:=Range("Calendar!l8:m15"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n4:o5"), CopyToRange:=Range("Calendar!n8:o15"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p4:q5"), CopyToRange:=Range("Calendar!p8:q15"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r4:s5"), CopyToRange:=Range("Calendar!r8:s15"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t4:u5"), CopyToRange:=Range("Calendar!t8:u15"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v4:w5"), CopyToRange:=Range("Calendar!v8:w15"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x4:y5"), CopyToRange:=Range("Calendar!x8:y15"), Unique:=False
        
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l16:M17"), CopyToRange:=Range("Calendar!l20:m27"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n16:o17"), CopyToRange:=Range("Calendar!n20:o27"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p16:q17"), CopyToRange:=Range("Calendar!p20:q27"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r16:s17"), CopyToRange:=Range("Calendar!r20:s27"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t16:u17"), CopyToRange:=Range("Calendar!t20:u27"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v16:w17"), CopyToRange:=Range("Calendar!v20:w27"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x16:y17"), CopyToRange:=Range("Calendar!x20:y27"), Unique:=False
        


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l28:M29"), CopyToRange:=Range("Calendar!l32:m39"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n28:o29"), CopyToRange:=Range("Calendar!n32:o39"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p28:q29"), CopyToRange:=Range("Calendar!p32:q39"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r28:s29"), CopyToRange:=Range("Calendar!r32:s39"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t28:u29"), CopyToRange:=Range("Calendar!t32:u39"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v28:w29"), CopyToRange:=Range("Calendar!v32:w39"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x28:y29"), CopyToRange:=Range("Calendar!x32:y39"), Unique:=False
        
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l40:M41"), CopyToRange:=Range("Calendar!l44:m51"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n40:o41"), CopyToRange:=Range("Calendar!n44:o51"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p40:q41"), CopyToRange:=Range("Calendar!p44:q51"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r40:s41"), CopyToRange:=Range("Calendar!r44:s51"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t40:u41"), CopyToRange:=Range("Calendar!t44:u51"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v40:w41"), CopyToRange:=Range("Calendar!v44:w51"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x40:y41"), CopyToRange:=Range("Calendar!x44:y51"), Unique:=False
        
        
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l52:M53"), CopyToRange:=Range("Calendar!l56:m63"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n52:o53"), CopyToRange:=Range("Calendar!n56:o63"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p52:q53"), CopyToRange:=Range("Calendar!p56:q63"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r52:s53"), CopyToRange:=Range("Calendar!r56:s63"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t52:u53"), CopyToRange:=Range("Calendar!t56:u63"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v52:w53"), CopyToRange:=Range("Calendar!v56:w63"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x52:y53"), CopyToRange:=Range("Calendar!x56:y63"), Unique:=False
        
        
        
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!l64:M65"), CopyToRange:=Range("Calendar!l68:m75"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!n64:o65"), CopyToRange:=Range("Calendar!n68:o75"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!p64:q65"), CopyToRange:=Range("Calendar!p68:q75"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!r64:s65"), CopyToRange:=Range("Calendar!r68:s75"), Unique:=False


Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!t64:u65"), CopyToRange:=Range("Calendar!t68:u75"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!v64:w65"), CopyToRange:=Range("Calendar!v68:w75"), Unique:=False
        
Sheets("RequestLog").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!x64:y65"), CopyToRange:=Range("Calendar!x68:y75"), Unique:=False
End Sub

 

[James006]

Hello,

Thanks for testing following macro :

Sub RefineAdvFilter()
Dim i As Long
Dim j As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
  For i = 4 To 64 Step 12
    For j = 12 To 24 Step 2
      With Sheets("RequestLog").Columns("A:G")
        .AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("Calendar!" & Nb2Let(j) & i & ":" & Nb2Let(j + 1) & i + 1), CopyToRange:=Range("Calendar!" & Nb2Let(j) & i + 4 & ":" & Nb2Let(j + 1) & i + 11), Unique:=False
      End With
    Next j
  Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Hope this will help

 

[raccoon588]

This works perfectly. Thank you for ALL your help with this!

 

[James006]

This works perfectly. Thank you for ALL your help with this!

Thanks for the feedback ...You are welcome ...

Pleased to hear you could solve your problem ...