filter same column to get data between two dates

dss28

Board Regular
Joined
Sep 3, 2020
Messages
146
Office Version
  1. 2007
Platform
  1. Windows
I am trying to build a code to filter column D (date Column) to extract data between two dates which i specify in a userform in two textboxes (start and end date).

when I run the code i only get the headers in the newly created shet but rest of the data is not filtered/copied/pasted (?).

I tried date formats such as dd/mm/yyyy, mm/dd/yyyy, mmm/dd/yyyy, number format but I am getting only the header part.

request for help to sort the issue.

my code is as follows:


VBA Code:
Sub FilterBetweenDates()

'' get data between two dates from TotalReport and save in new work book


Application.ScreenUpdating = False

Dim X As Range

Dim rng As Range

Dim last As Long

Dim sht As String

Dim newBook As Excel.Workbook

Dim Workbk As Excel.Workbook



'Specify sheet name in which the data is stored

sht = "TotalReport"



'Workbook where VBA code resides

Set Workbk = ThisWorkbook



'New Workbook

Set newBook = Workbooks.Add(xlWBATWorksheet)

Workbk.Activate



'specify filter range in the following code

last = Workbk.Sheets(sht).Cells(Rows.Count, "B1:H").End(xlUp).row



With Workbk.Sheets(sht)

Set rng = .Range("B1:H" & last)

End With


' filtering code =========== date in column D ========================================

With Sheet19

With rng

.AutoFilter

.AutoFilter Field:=4, Criteria1:=">=" & UserForm36.TextBox1.Value, Operator:=xlAnd 'start date"

.AutoFilter Field:=4, Criteria2:="<=”&UserForm36.TextBox2.Value, Operator:=xlAnd 'end date"


'=============== copy=============================

With rng1

.SpecialCells(xlCellTypeVisible).Copy

'=====add new workbook and paste the filtered data============

Application.Workbooks.Add 1

newBook.Activate

Range("B3").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

End With


Workbk.Sheets(sht).AutoFilterMode = False


With Application

.CutCopyMode = False

.ScreenUpdating = True


End With


End With


MsgBox " A new workbook has been created, name the file and save", vbOKOnly, "Report between Two Dates "


End With


End Sub
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,348
Office Version
  1. 2010
Platform
  1. Windows
Try this
VBA Code:
Sub FilterBetweenDates()
'' get data between two dates from TotalReport and save in new work book
Application.ScreenUpdating = False

Dim X As Range
Dim rng As Range
Dim last As Long
Dim sht As Worksheet
Dim newBook As Workbook
Dim Workbk As Workbook

'Workbook where VBA code resides
Set Workbk = ThisWorkbook
'Specify sheet with the data
Set sht = Workbk.Sheets("TotalReport")

'specify filter range
With sht
    last = .Range("B:H").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set rng = .Range("B1:H" & last)
End With

' filter and copy
With rng
    .AutoFilter
    .AutoFilter Field:=3, Criteria1:=">=" & CDate(UserForm36.TextBox1.Value), Operator:=xlAnd, _
                          Criteria2:="<=" & CDate(UserForm36.TextBox2.Value)
    .SpecialCells(xlCellTypeVisible).Copy
End With

'Add New Workbook
Set newBook = Workbooks.Add
Range("B3").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Workbk.Activate
sht.AutoFilterMode = False

With Application
    .CutCopyMode = False
    .ScreenUpdating = True
End With

MsgBox " A new workbook has been created, name the file and save", vbOKOnly, "Report between Two Dates "

End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

dss28

Board Regular
Joined
Sep 3, 2020
Messages
146
Office Version
  1. 2007
Platform
  1. Windows
Try this
VBA Code:
Sub FilterBetweenDates()
'' get data between two dates from TotalReport and save in new work book
Application.ScreenUpdating = False

Dim X As Range
Dim rng As Range
Dim last As Long
Dim sht As Worksheet
Dim newBook As Workbook
Dim Workbk As Workbook

'Workbook where VBA code resides
Set Workbk = ThisWorkbook
'Specify sheet with the data
Set sht = Workbk.Sheets("TotalReport")

'specify filter range
With sht
    last = .Range("B:H").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set rng = .Range("B1:H" & last)
End With

' filter and copy
With rng
    .AutoFilter
    .AutoFilter Field:=3, Criteria1:=">=" & CDate(UserForm36.TextBox1.Value), Operator:=xlAnd, _
                          Criteria2:="<=" & CDate(UserForm36.TextBox2.Value)
    .SpecialCells(xlCellTypeVisible).Copy
End With

'Add New Workbook
Set newBook = Workbooks.Add
Range("B3").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Workbk.Activate
sht.AutoFilterMode = False

With Application
    .CutCopyMode = False
    .ScreenUpdating = True
End With

MsgBox " A new workbook has been created, name the file and save", vbOKOnly, "Report between Two Dates "

End Sub

no luck either with this sir...
only the header is copied on the new workbook created.

do the textboxes 1 and 2 on the userform need any coding to transfer the figures as dates?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,644
Office Version
  1. 365
Platform
  1. Windows
Try replacing the "filter and copy" with..end with section in @NoSparks, code with this.
VBA Code:
    ' filter and copy
    With rng
        .AutoFilter
        .AutoFilter Field:=3, Criteria1:=">=" & CLng(CDate(UserForm36.TextBox1.Value)), Operator:=xlAnd, _
                              Criteria2:="<=" & CLng(CDate(UserForm36.TextBox2.Value))
        .Copy
    End With
 

dss28

Board Regular
Joined
Sep 3, 2020
Messages
146
Office Version
  1. 2007
Platform
  1. Windows
Try replacing the "filter and copy" with..end with section in @NoSparks, code with this.
VBA Code:
    ' filter and copy
    With rng
        .AutoFilter
        .AutoFilter Field:=3, Criteria1:=">=" & CLng(CDate(UserForm36.TextBox1.Value)), Operator:=xlAnd, _
                              Criteria2:="<=" & CLng(CDate(UserForm36.TextBox2.Value))
        .Copy
    End With
If I transfer the userform textbox start and end date values in the sheet ... say to M1 and N1 cell and apply the autofilter code as below with reference to these cells, it is working.
Also while doing this I changed the format of the dates to mm/dd/yyyy

VBA Code:
Dim lngStart As Long, lngEnd As Long

lngStart = Range("M1").Value 'assume this is the start date

lngEnd = Range("N1").Value 'assume this is the end date

Range("A1:J9999").AutoFilter field:=4, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd, Operator:=xlAnd

Range("A1:J9999").SpecialCells(xlCellTypeVisible).Copy

Now need to change the range ("A1:J9999") to "End(xlUp).row" type.

will update and seek guidance on this further in case I am stuck somewhere.. Do stay back with me... Thanks.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,348
Office Version
  1. 2010
Platform
  1. Windows
Now need to change the range ("A1:J9999") to "End(xlUp).row" type.
If there is no data beyond row 9999 you only need the columns as per finding last in post 11
 
Solution

dss28

Board Regular
Joined
Sep 3, 2020
Messages
146
Office Version
  1. 2007
Platform
  1. Windows
If there is no data beyond row 9999 you only need the columns as per finding last in post 11
done up
essentials required to fetch the data,
1) date format in mm/dd/yyyy in the date column and userform textbox entry
and then reformated the date column back to dd/mm/yyyy
thanks everybody
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,863
Messages
5,766,814
Members
425,379
Latest member
thedoctor00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top