I have been struggling with this for several days now and I would appreciate any help you may be able to provide. I have a sheet with 69 columns and 6600 rows called Raw Data. I also have a sheet called Filtered Data. I have a drop down menu on the Filtered Data sheet in cell B4. The list in the drop down menu corresponds to the columns of data in the Raw Data sheet. I use cell B5 to enter a min value and cell B6 to enter a min value. I want to filter the Raw Data sheet according to the column selected via the drop down menu such that the values in that column are greater than or equal to my min value and less than or equal to my max value. I have written the code and it does not filter even though I see data that meets my criteria in the Raw Data list. Any help would be greatly appreciated. Thank you very much.
Private Sub ExtractData(ByVal Filter As Range) 'Dim variables Dim LR As Long, NR As Long Dim filterItem As String Dim minValue As Variant, maxValue As Variant Dim colNum As Integer Dim rng As Range, min As Range, max As Range Dim shSource As Worksheet Dim shDest As Worksheet 'Set range and source and target worksheets Set shSource = ThisWorkbook.Sheets("Raw Data") Set shDest = ThisWorkbook.Sheets("Filtered Data") 'shSource.Range("D11:BP11") is the list of all possible drop down menu items Set rng = shSource.Range("D11:BP11") 'Set min and max filter cells Set min = shDest.Range("B5") Set max = shDest.Range("B6") 'Define min and max filter values minValue = shDest.Range("B5").Value maxValue = shDest.Range("B6").Value filterItem = Filter.Value 'Determine which column contains the filter category colNum = Application.Match(filterItem, rng, 0) If Not IsError(colNum) Then Select Case colNum Case 1 To 3: 'Columns B to F min.NumberFormat = "@" 'string format max.NumberFormat = "@" Case 4 To 11, 14, 22 To 23, 29, 33 To 37, 46 To 47, 57, 60 To 61, 63, 65: min.NumberFormat = "0.00" 'number format max.NumberFormat = "0.00" Case Else: min.NumberFormat = "0.00%" 'percentage format max.NumberFormat = "0.00%" End Select NR = shDest.Range("A" & rows.Count).End(xlUp).Offset(1).Row 'Go to cell below last used cell in column A With shSource LR = .Cells(rows.Count, "A").End(xlUp).Row 'Last row of column A .AutoFilterMode = False With .Range("A12" & LR) .AutoFilter Field:=colNum, Criteria1:=">=" & minValue, Operator:=xlAnd, Criteria2:="<=" & maxValue, VisibleDropDown:=False .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy shDest.Range("A" & NR) .AutoFilter End With End With Else MsgBox filterItem + " filter criterion was not found." End If shDest.Activate End Sub