Excel/formulas

Advertisement


Question
Hi Tom

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
bk1.Activate
Set Sh2 = bk1.Worksheets("salesrpt")

Sh2.unprotect Password:=Sheet1.Range("M1")

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

   End If
   End If
   
   Sh2.Protect Password:=Sheet1.Range("M1")
   
   Application.Run "sortsalesrpt"

   Application.ScreenUpdating = True

If Target.Address = "$O$7" Then
 If Len(Trim(Target.Value)) = 0 Or LCase(Target.Value) = LCase("select date") Then
    Exit Sub

   Application.Run "Filter"

End If
End If
End Sub

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

With salesrpt

       
   On Error Resume Next
   ActiveSheet.unprotect Password:=Sheet1.Range("M1")
   
         .AutoFilterMode = False

         With .Range("C6:L6")

         .AutoFilter

         .AutoFilter Field:=1, Criteria1:=selectdate
         
       
   On Error Resume Next
   ActiveSheet.Protect Password:=Sheet1.Range("M1")

   End With
   End With
End Sub

Answer
Tony,


YOu use

With salesrpt    

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

With salesrpt

to

With wsOrder  


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.  

--
regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.