jeudi 29 septembre 2016

AutoFilter via IF and multiple critera

This is my first post on this site so hoping to find an answer. I have an Excel form that users can fill out and click "Run Report". The report will grab data from a master tab and paste it on another tab. This is accomplished via an IF statement and auto filter. At the time, I had two fields and users could select either Field 1, Field 2, or both. Depending on the inputs, the formula would auto filter data appropriately.

Now, status codes have been incorporated to the form. I have created check boxes (form control, not activex) and linked the box to a cell. If the box is checked, the cell updates to "True", if it's not checked the cell shows "false".

I am looking to write an IF statement that auto filters the master data and pastes it onto a new sheet. Cell E9 and E13 (Sheet: Search Form) are drop downs (data validation). The check boxes are linked in Column S (S1-S23 - on Sheet: Search Form). I need the IF statement that auto filters the data (Sheet All Call Center Detail) in Column 6 by any value in E9, auto filter Column 7 by any value in E13, and auto filter Column 13 by any TRUE cells in S:S (Column S is on the Search Form sheet).

Please ask any questions as I know my explanation is a bit overwhelming. Here is the code:

Sub Add_Sheet()

Dim LastRow As Long
Dim Rng As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim i As Long, wsName As String, temp As String

Sheets("All Call Center Detail").Select
LastRow = Sheets("All Call Center Detail").Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("A1:BT" & LastRow)

Sheets("Search Form").Select
Set Rng1 = Range("E9")
Set Rng2 = Range("E13")

Sheets.Add After:=ActiveSheet
ActiveSheet.Name = ("Results")

Sheets("All Call Center Detail").Select

If Not Rng1 = "" And Not Rng2 = "" And Not Rng3 = "False" Then

    Sheets("All Call Center Detail").Select
    Rng.AutoFilter Field:=6, Criteria1:=Rng1
    Rng.AutoFilter Field:=7, Criteria1:=Rng2
    Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Results").Range("A1")

    ElseIf Rng1 = "" Then
    Sheets("All Call Center Detail").Select
    Rng.AutoFilter Field:=7, Criteria1:=Rng2
    Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Results").Range("A1")

    ElseIf Rng2 = "" Then
    Sheets("All Call Center Detail").Select
    Rng.AutoFilter Field:=6, Criteria1:=Rng1
    Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Results").Range("A1")

End If

Sheets("All Call Center Detail").Activate
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter

Sheets("Results").Activate
ActiveSheet.Columns.AutoFit
wsName = Format(Date, "mmddyy")

    If WorksheetExists(wsName) Then
    temp = Left(wsName, 6)
    i = 1
    wsName = temp & "_" & i
    Do While WorksheetExists(wsName)
    i = i + 1
    wsName = temp & "_" & i
    Loop
    End If

ActiveSheet.Name = wsName
Range("A1").Select

End Sub

Aucun commentaire:

Enregistrer un commentaire