PDA

View Full Version : Selecting a range based on a date range



TomParker13
05-01-2013, 01:06 AM
Hi, I am new to this forum and need some help with some code, I want to be able to use a cmdsubmitfunction where when entering a date into a text box and clicking cmdsubmit the code is able to select a range of rows based on the date in column A containing the current days date -6days then take that selection and paste into a body of an email. Any ideas??

Bob Phillips
05-01-2013, 01:20 AM
I think an example workbook would help, with details of what data is to be extracted from that example.

TomParker13
05-01-2013, 04:44 AM
Date Entered Contract Service Call Severity Service Site 30/04/2013 21/04/2013

TomParker13
05-01-2013, 04:49 AM
Hi, Having issues adding the workbook, basically rows of data are entered throughout a 7day period I want the code to select a range based on the current date and each date previous upto 6days so the range would select all data entries which contained the last 7days dates. in Column A E.g. if I ran it today the code would select all rows which contained Todays date (01/05/13) and dates 6 days back - 30/04/13, 29/04/13, 28/04/13, 27/04/13, 26/04/13 25/04/13.

If that adds more clarity to my query.

Bob Phillips
05-01-2013, 05:23 AM
Sub CopyData()
Dim rng As Range
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range("A1").Resize(lastrow)
rng.AutoFilter Field:=1, _
Criteria1:="<=" & Format(Date, .Range("A2").NumberFormat), _
Operator:=xlAnd, _
Criteria2:=">" & Format(Date - 7, .Range("A2").NumberFormat)
rng.SpecialCells(xlCellTypeVisible).Copy

'then do something with the copied range
End With
End Sub