key in date and filter the data in a week

key in date and filter the data in a week

• Kevin Barnes

  Hi,
  Am i only need to copy this code into the vba and not doing any stuff other than that including creating a list box. I have problem creating a drop down selection box. Please help.
  Thanks!
  
  

• DaveDB

  Hi Pippen

  I copied your weeks table into Sheet1 starting at A1, I created a dropdown selection box in K1 ( with various dates), and copied your sample data into sheet2 starting in F1. The below code picks up any change to Sheet1 K1 (the dropdown box) and tries to find the range of dates, if the range is found it applies the Excel filter to the data on sheet 2

  Hope this helps

  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim X As Long
  Dim DateFound As Boolean
  Dim SelectedDate, StartDate, EndDate As Date
  If Intersect(Target, Sheet1.Range("K1")) Then
  X = 1
  DateFound = False
  SelectedDate = Sheet1.Cells(1, 11).Value
  While Left$(Sheet1.Cells(X, 1).Value, 4) = "Week" And Not DateFound
  If ((SelectedDate >= Sheet1.Cells(X, 2).Value) And (SelectedDate <= Sheet1.Cells(X, 3).Value)) Then
  StartDate = Sheet1.Cells(X, 2).Value
  EndDate = Sheet1.Cells(X, 3).Value
  DateFound = True
  Else
  X = X + 1
  End If
  Wend
  If DateFound Then
  Sheet2.Activate
  Sheet2.Range("F1:H1").Select
  Sheet2.Range(Selection, Selection.End(xlDown)).Select
  Selection.AutoFilter
  Selection.AutoFilter Field:=1, Criteria1:=">=" & Format(StartDate, "dd-mmm-yyyy"), Operator:=xlAnd _
  , Criteria2:="<=" & Format(EndDate, "dd-mmm-yyyy")
  End If
  End If
  End Sub

  
  

• Yuhang

  ADG wrote:

  Hi Which office product are you working with If you are in Access this is quite straight forward, if you are in excel the approach will be different Regards ADG

  
  
  I am using excel. That's why i think i need a macro to accomplish this difficult task. :) Thanks..
  
  

• mertkan65

  Hi

  Which office product are you working with If you are in Access this is quite straight forward, if you are in excel the approach will be different

  Regards

  ADG

  
  

• eexportc

  ADG wrote:

  Do the filters appear at the top of the columns on sheet2 If so go into the custom option on the date column and see what is in there. If the filters do not appear then I suspect that the data in you dropdown is not being found in your table, in which case check all your dates are actually date formats

  
  
  
  
  Hi,
  Thanks! Sorry.. the reason that it couldn't filter out itself was i didn't link the sheets properly and i didn't copy the code to the correct work sheet. sorry.. now i have solved this problem with your help. I have changed and improve the code a bit, so that i can fit my work better. Anyway, thanks a lot! really thank you. thanks for your time. :)
  
  

• David C Baldauff

  Do the filters appear at the top of the columns on sheet2 If so go into the custom option on the date column and see what is in there. If the filters do not appear then I suspect that the data in you dropdown is not being found in your table, in which case check all your dates are actually date formats
  

• Pockey

  I managed to create a drop down list. However, the sheet2 doesn't filter itself. why
  Thanks and really appreciate your time for replying me. Thanks again.
  
  
  
  

  ADG wrote:

  Hi Pippen I copied your weeks table into Sheet1 starting at A1, I created a dropdown selection box in K1 ( with various dates), and copied your sample data into sheet2 starting in F1. The below code picks up any change to Sheet1 K1 (the dropdown box) and tries to find the range of dates, if the range is found it applies the Excel filter to the data on sheet 2 Hope this helps Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long Dim DateFound As Boolean Dim SelectedDate, StartDate, EndDate As Date If Intersect(Target, Sheet1.Range("K1")) Then X = 1 DateFound = False SelectedDate = Sheet1.Cells(1, 11).Value While Left$(Sheet1.Cells(X, 1).Value, 4) = "Week" And Not DateFound If ((SelectedDate >= Sheet1.Cells(X, 2).Value) And (SelectedDate <= Sheet1.Cells(X, 3).Value)) Then StartDate = Sheet1.Cells(X, 2).Value EndDate = Sheet1.Cells(X, 3).Value DateFound = True Else X = X + 1 End If Wend If DateFound Then Sheet2.Activate Sheet2.Range("F1:H1").Select Sheet2.Range(Selection, Selection.End(xlDown)).Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:=">=" & Format(StartDate, "dd-mmm-yyyy"), Operator:=xlAnd _ , Criteria2:="<=" & Format(EndDate, "dd-mmm-yyyy") End If End If End Sub

  
  
• key in date and filter the data in a week