Loop through Multiple Worksheets, with autofilter, for different Criteria

[Cooliozar]

I've searched several forums over the last few days and have been unable to find exactly what I'm trying to do. Apologies if I missed it somewhere in this forum.

Currently, I have a functioning macro that will autofilter each worksheet for the specified values from the main "input" worksheet however, they are each contained within their own "with" statement. There are currently 9 separate "with" statements (1 for each worksheet) and I'm needing to expand this macro to possibly 40 different worksheets. Each worksheet has different values in different cloumns that need to be pulled based on filtered criteria. The question is, can I use VBA to combine these calls to each worksheet in a "for loop" or some other way? If so, how?

The following represents code I currently have with values changed for simplicity:

Sub Calculations()

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
For i = 3 To ActiveWorkbook.Worksheets.Count
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If
Next i

Dim rInput1 As Range, rInput2 As Range, rInput3 As Range, rInput4 As Range, rInput5 As Range, rInput6 As Range, rInput7 As Range, rInput8 As Range
Dim rRng1 As Range, rRng2 As Range, rRng3 As Range, rRng4 As Range, rRng5 As Range, rRng6 As Range, rRng7 As Range, rRng8 As Range, rRng9 As Range
Dim vReturn1 As Double, vReturn2 As Double, vReturn3 As Double, vReturn4 As Double, vReturn5 As Double, vReturn6 As Double, vReturn7 As Double, vReturn8 As Double, vReturn9 As Double

Set rInput1= Worksheets("Input Sheet").Range("C7")
Set rInput2 = Worksheets("Input Sheet").Range("C8")
Set rInput3 = Worksheets("Input Sheet).Range("C9")
Set rInput4 = Worksheets("Input Sheet").Range("C10")
Set rInput5 = Worksheets("Input Sheet).Range("C11")
Set rInput6 = Worksheets("Input Sheet").Range("C12")
Set rInput7 = Worksheets("Input Sheet").Range("C13")
Set rInput8 = Worksheets("Input Sheet").Range("C14")
 
Set rRng1 = Worksheets("WORKSHEET1").Range("A1:K5")
Set rRng2 = Worksheets("WORKSHEET2").Range("A1:N13")
Set rRng3 = Worksheets("WORKSHEET3").Range("A1:N117")
Set rRng4 = Worksheets("WORKSHEET4").Range("A1:O5")
Set rRng5 = Worksheets("WORKSHEET5").Range("A1:O3")
Set rRng6 = Worksheets("WORKSHEET6).Range("A1:L598")
Set rRng7 = Worksheets("WORKSHEET7").Range("A1:N76")
Set rRng8 = Worksheets("WORKSHEET8").Range("A1:N211")
Set rRng9 = Worksheets("WORKSHEET9").Range("A1:P111")
 
    
With rRng1
    .AutoFilter Field:=1, Criteria1:=rInput1.Value
    
    .AutoFilter Field:=3, Criteria1:="<=" & rInput3
    .AutoFilter Field:=4, Criteria1:=">=" & rInput3
    
    .AutoFilter Field:=5, Criteria1:="<=" & rInput2
    .AutoFilter Field:=6, Criteria1:=">=" & rInput2
    
     .AutoFilter Field:=5, Criteria1:="<=" & rInput4
    .AutoFilter Field:=6, Criteria1:=">=" & rInput4
    
     .AutoFilter Field:=10, Criteria1:="<=" & rInput5
    .AutoFilter Field:=11, Criteria1:=">=" & rInput5
    
    Worksheets("WORKSHEET1").Activate
    
    Range("I2", Cells(Rows.Count, "I").End(xlUp + 1)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    
    vReturn3 = Selection.Value

End With


With rRng2
    .AutoFilter Field:=1, Criteria1:=rInput1.Value
    
    .AutoFilter Field:=2, Criteria1:="<=" & rInput7
    .AutoFilter Field:=10, Criteria1:=">=" & rInput7
    
    Worksheets("WORKSHEET2").Activate
    
    Range("L2", Cells(Rows.Count, "L").End(xlUp + 1)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    
    vReturn9 = Selection.Value
    
End With

This method continues on until all 9 worksheets have been filtered for (and selected) different criteria, while rInput1 always remains the 1st autofilter as well as the 1st "Field".  The macro ends with populating return values into specific cells on the Input Sheet and then a calculation is done and presented into a cell and the macro ends with the following:


Worksheets("Input Sheet").Activate

Application.EnableEvents = True

End Sub

Since all the criteria varies within each rRng I don't konw how to roll up this logic into a single loop.

I appreciate any help that anyone can provide. Please let me know if I need to provide more information. Thanks!

Microsoft Misual Basic for Applications 7.1
Excel 2013
Windows 7 SP1
X64-based PC
Quad 2.40 GHz Intel Core i5-6300U
8 GB DDR

 

[Fluff]

You're welcome