Select Date Range
 

I have a worksheet that has column a (dates), column b (dollars collected),
and column c (method). I am trying to figure out how to right a code that
would pop-up two inputbox's, the first asking for the start date and the
second requesting the end date, then this range of dates would then tell the
code to select the entire row for any date inbetween and copy it to a new
worksheet. I can figure out the input box and the copy and paste portion but
not the middle part where the code selects everything within the date range.
Example: start is 2/1/06, end is 3/1/06. then go to the spreadsheet and even
if the first entry is dated 2/3 and last entry is 2/28 that it would grab
those and all in between and paste to worksheet2?

Select Date Range
 

Hi,
Try this: it assumes dates are column A with header row and copies
to column A, row 2 of second worksheet.


HTH


Sub GetData()

Dim sDate As Date, fDate As Date
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Sheet1") '<== Change as required
Set ws2 = Worksheets("Sheet2") '<== Change as required

ws1.Activate
With ws1
'assumes dates are in colum A
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row

sDate = "01/02/2006" ' <=== From your input box
fDate = "28/02/2006" ' <=== From your input box

Set dateRng = Range("a1:a" & lastrow)


r = Application.Match(CLng(sDate), dateRng, 1)
If IsError(r) Then
frow = 2 ' first row i.e. start date is before first date in column A
Else
frow = r
End If
lrow = Application.Match(CLng(fDate), dateRng, 1)

.Cells(frow, 1).Resize(lrow - frow + 1).EntireRow.Copy ws2.Range("a2")
End With

End Sub


"Kryer" wrote:

I have a worksheet that has column a (dates), column b (dollars collected),
and column c (method). I am trying to figure out how to right a code that
would pop-up two inputbox's, the first asking for the start date and the
second requesting the end date, then this range of dates would then tell the
code to select the entire row for any date inbetween and copy it to a new
worksheet. I can figure out the input box and the copy and paste portion but
not the middle part where the code selects everything within the date range.
Example: start is 2/1/06, end is 3/1/06. then go to the spreadsheet and even
if the first entry is dated 2/3 and last entry is 2/28 that it would grab
those and all in between and paste to worksheet2?

Select Date Range
 

Thank You lots. I would never have gotten this one figured out with out your
help.

"Toppers" wrote:

Hi,
Try this: it assumes dates are column A with header row and copies
to column A, row 2 of second worksheet.


HTH


Sub GetData()

Dim sDate As Date, fDate As Date
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Sheet1") '<== Change as required
Set ws2 = Worksheets("Sheet2") '<== Change as required

ws1.Activate
With ws1
'assumes dates are in colum A
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row

sDate = "01/02/2006" ' <=== From your input box
fDate = "28/02/2006" ' <=== From your input box

Set dateRng = Range("a1:a" & lastrow)


r = Application.Match(CLng(sDate), dateRng, 1)
If IsError(r) Then
frow = 2 ' first row i.e. start date is before first date in column A
Else
frow = r
End If
lrow = Application.Match(CLng(fDate), dateRng, 1)

.Cells(frow, 1).Resize(lrow - frow + 1).EntireRow.Copy ws2.Range("a2")
End With

End Sub


"Kryer" wrote:

I have a worksheet that has column a (dates), column b (dollars collected),
and column c (method). I am trying to figure out how to right a code that
would pop-up two inputbox's, the first asking for the start date and the
second requesting the end date, then this range of dates would then tell the
code to select the entire row for any date inbetween and copy it to a new
worksheet. I can figure out the input box and the copy and paste portion but
not the middle part where the code selects everything within the date range.
Example: start is 2/1/06, end is 3/1/06. then go to the spreadsheet and even
if the first entry is dated 2/3 and last entry is 2/28 that it would grab
those and all in between and paste to worksheet2?

Select Date Range
 

I did have a question about the code that I am not fully understanding. That
is with the frow and lrow. The code is writtne with application.match, but I
cannot find any help file on this snipet. So i ask you then what the
variables mean. I have down the CLng(sDate), but not what the dateRng or the
1 have to do with the overall code. Could you break this out a little bit
more for me?

"Toppers" wrote:

Hi,
Try this: it assumes dates are column A with header row and copies
to column A, row 2 of second worksheet.


HTH


Sub GetData()

Dim sDate As Date, fDate As Date
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Sheet1") '<== Change as required
Set ws2 = Worksheets("Sheet2") '<== Change as required

ws1.Activate
With ws1
'assumes dates are in colum A
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row

sDate = "01/02/2006" ' <=== From your input box
fDate = "28/02/2006" ' <=== From your input box

Set dateRng = Range("a1:a" & lastrow)


r = Application.Match(CLng(sDate), dateRng, 1)
If IsError(r) Then
frow = 2 ' first row i.e. start date is before first date in column A
Else
frow = r
End If
lrow = Application.Match(CLng(fDate), dateRng, 1)

.Cells(frow, 1).Resize(lrow - frow + 1).EntireRow.Copy ws2.Range("a2")
End With

End Sub


"Kryer" wrote:

I have a worksheet that has column a (dates), column b (dollars collected),
and column c (method). I am trying to figure out how to right a code that
would pop-up two inputbox's, the first asking for the start date and the
second requesting the end date, then this range of dates would then tell the
code to select the entire row for any date inbetween and copy it to a new
worksheet. I can figure out the input box and the copy and paste portion but
not the middle part where the code selects everything within the date range.
Example: start is 2/1/06, end is 3/1/06. then go to the spreadsheet and even
if the first entry is dated 2/3 and last entry is 2/28 that it would grab
those and all in between and paste to worksheet2?