Monday, December 27, 2010

Apply Filter on Dates And copy the output to new workbook

If you want to apply filter on dates and then copy the result to new workbook.

1) Applying Autofilter on dates
2) Copy the Filtered data to new workbook

For Example you have a data sheet like

You want to filter the records who's dates are between cell H1 & M1

Here is the code
Sub filterbetweendates()
Dim i As Long

Dim wkb, wkb1 As Workbook
Set wkb1 = ThisWorkbook

i = Range("a2").End(xlDown).Row

' this if condition will remove if any filter applied in sheet 1
If wkb1.Sheets(1).FilterMode Then
End If

' this if condition will check if the start date entered by the user is present in col c or not
If Application.WorksheetFunction.CountIf(Sheets(1).Range("c:c"), Sheets(1).Cells(1, 8).Value) = 0 Then
Sheets(1).Cells(1, 8).Value = ""
MsgBox "Enter the date in cell H1 Which is present in the column C"
Exit Sub
End If

' this if condition will check if the end date entered by the user is present in col c or not and end date > start date

If Application.WorksheetFunction.CountIf(Sheets(1).Range("c:c"), Sheets(1).Cells(1, 13).Value) = 0 Or Sheets(1).Cells(1, 13).Value < Sheets(1).Cells(1, 8).Value Then

 Sheets(1).Cells(1, 13).Value = ""

MsgBox "Enter the date in cell m1 Which is present in the column C and it should be greater than start date "
 Exit Sub
End If

 'apply filter

 wkb1.Sheets(1).Range("$A$2:$C$" & i).AutoFilter Field:=3, Criteria1:= _ "&>=" & Format(wkb1.Sheets(1).Cells(1, 8).Value, "DD-MMM-yy"), Operator:=xlAnd, Criteria2:="&<=" &Format(wkb1.Sheets(1).Cells(1, 13).Value, "DD-MMM-yy")

Sheets(1).Range("$A$2:$C$" & i).SpecialCells(xlCellTypeVisible).Select


 ' new workbook is added

Workbooks.Add Set wkb = ActiveWorkbook '

 filtered data is pasted on sheet1 of a new workbook

wkb.Sheets(1).Select ActiveSheet.Paste

 ' save new workbook and you can change the file name

 'wkb.SaveAs ThisWorkbook.Path & "\New_Week_Report" & ".xls"
 ' new workbook is closed

 If wkb1.Sheets(1).FilterMode Then


 End If

 End Sub

 Download Sample Workbook Click Here

1 comment:

  1. It would be great if you can also give option to search post similar to

    As the data is increasing its better to give a search option in website