Hope all is well - i have this code am struggling with and hoping you might be able to help out...
i have the following code, and what we want done is the following:
we have a dropdown list in O7 to select a month, once we do that we want the worksheet to filter only those date that match via vba. So if we select june 16 all those clients who bought in June 16 to be filtered and shown. The code seems to go through the process but doesnt sort, hoping you can assist
below is the code we are using -
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sh2 As Worksheet
Dim bk1 As Workbook
Set bk1 = ThisWorkbook
Set Sh2 = bk1.Worksheets("salesrpt")
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
If LCase(Target.Value) = "x" Then
Range("A2:L2").Copy Cells(Target(1).Row, "A")
Application.ScreenUpdating = False
Application.ScreenUpdating = True
If Target.Address = "$O$7" Then
If Len(Trim(Target.Value)) = 0 Or LCase(Target.Value) = LCase("select date") Then
and the macro to run Filter is as follows:
selectdate = range O7
Private Sub Filter()
Dim wb As Workbook
Dim wsOrder As Worksheet
Set wb = ThisWorkbook
Set wsOrder = wb.Sheets("salesrpt")
selectdate = wsOrder.Range("selectsalesdate").Value
On Error Resume Next
.AutoFilterMode = False
.AutoFilter Field:=1, Criteria1:=selectdate
On Error Resume Next
but salesrpt is never defined. This probably raises an error, but you have On Error Resume Next so you never see the error and the code executes but does nothing.
try changing the
and for testing, comment out your error handling so you can see whether the code actually runs or not. I tested the code that applies the filter and it worked.
However, I didn't test it with a date and I have no idea what you have stored in column C of your data and how that compares with what will be contained in the variable "selectdate"
I would turn on the macro recorder while you manually set a criteria that works in your table and see how it is recorded. It may need to do a greater than or equal to (1 Jun 2016) and less than or equal to (30 Jun 2016) criteria if you actually have dates stored in column C.