This is my first post on the site so please forgive any posting errors. I am working on a macro that creates and adds data to an array. The array then filters based on the results. The issue is that the array runs but only filters based off of the last field in the array. Please note the following:
* Column R contains True/False indicators. The true/false corresponds with a value in Column S.
* The IF statement cycles through and for every "True" found in Column R, it adds the value in Column S to the array - titled arrResults()
* If there are three values marked true, all three are added to arrResults() - as confirmed in the immediate window and the debug statement found in the code
* When the filter happens on Column 13, it only filters based only on the last field (3rd True).
Please take a look at the code and let me know any suggestions. I just need to know why AutoFilter only recognizes the last field in arrResults and does not recognize any of the others.
Option Explicit Sub Add_Sheet_Update() Dim LastRow As Long Dim Rng As Range, str1 As String, str2 As String Dim i As Long, wsName As String, temp As String Dim arrResults() With Sheets("All Call Center Detail") LastRow = .Cells(Rows.Count, 1).End(xlUp).Row Set Rng = .Range("A1:BT" & LastRow) End With With Sheets("Search Form") str1 = .Range("E9").Text str2 = .Range("E13").Text End With Dim x As Integer, y As Integer With Range("R1:S99") ' 2 columns, any # of rows For x = 1 To .Rows.Count If .Cells(x, 1) Then y = y + 1 ReDim Preserve arrResults(1 To y) arrResults(y) = .Cells(x, 2) End If Next x End With Sheets.Add After:=Sheets("Search Form") ActiveSheet.Name = ("Results") Sheets("All Call Center Detail").Select If y > 0 Then Rng.AutoFilter Field:=13, Criteria1:=arrResults If Not str1 = "" Then Rng.AutoFilter Field:=6, Criteria1:=str1 If Not str2 = "" Then Rng.AutoFilter Field:=7, Criteria1:=str2 Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Results").Range("A1") Application.CutCopyMode = False ActiveSheet.ShowAllData 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