Selecting a range based on a date range

[TomParker13]

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]

I think an example workbook would help, with details of what data is to be extracted from that example.

[TomParker13]

Date Entered Contract Service Call Severity Service Site 30/04/2013 21/04/2013

[TomParker13]

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]

[VBA]
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[/VBA]