Find if between dates
 

hey everyone

I have date/time in colunm A and data through DD and I would like some
code that would find all the rows that are between two dates/times and
compy them into a different sheet.

Any help would be awesome

Thanks

Find if between dates
 

Hi -- Try this, it should do the trick. Nothing fancy in here.

This code will copy all data in between rows B through DD the "lowDate" and
"highDate" that you specify. It assumes your dates are in Row A.

Let me know if you have any questions or need any help.

You need to change the "lowDate" and "highDate" below to match whatever
dates you want it to look between.

I hope this helps, and I hope I've been helpful. Please let me know.

Sub copy_data()
Dim lowDate As String
Dim highDate As String
Dim row As Integer
Dim skiprow As Integer

LastRow = Cells.Find("*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).row

lowDate = "1/9/2007"
highDate = "6/1/2007"
row = 0
skiprow = 0

Range("A1:A" & LastRow).Select

For Each Cell In Selection

If Cell.Value < lowDate Then
skiprow = Cell.row + 1
End If

If Cell.Value lowDate And Cell.Value < highDate Then
row = Cell.row
End If

Next

Range("B" & skiprow & ":DD" & row).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Range("A1").Select

End Sub


--
Best of Luck!,

Chris Van Nuys

Become more effective and efficient: Drive your business career by
mastering Excel!


" wrote:

hey everyone

I have date/time in colunm A and data through DD and I would like some
code that would find all the rows that are between two dates/times and
compy them into a different sheet.

Any help would be awesome

Thanks

Find if between dates
 

Hi!

Try and the macro below as an second suggestion.
It's more flexible and integrated.

'---------------------8<----------------------------
Sub CopyDataRowsByDate()
Dim dtmLowDate As Date
Dim dtmHiDate As Date
Dim rngCell As Range
Dim strSheetName As String
Dim wsTargetSheet As Worksheet

On Error Resume Next
Sheets(1).Activate 'Specify your data Worksheet
InputLowDate:
dtmLowDate = DateValue(InputBox("Specify LowDate"))
If dtmLowDate 0 Then
InputHiDate:
dtmHiDate = DateValue(InputBox("Specify HiDate"))
If dtmHiDate 0 Then
InputSheetName:
strSheetName = InputBox("Specify the name of target worksheet")
Set wsTargetSheet = Worksheets(strSheetName)
If Not wsTargetSheet Is Nothing Then
For Each rngCell In Range("A1:A" &
Range("A:A").SpecialCells(11).Row).Cells
If rngCell dtmLowDate And rngCell < dtmHiDate Then
Range("A" & rngCell.Row, "D" & rngCell.Row).Copy _
Destination:=wsTargetSheet _
.Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next rngCell
Else
If MsgBox("Invalid worksheet name.", _
vbCritical + vbOKCancel, "Worksheet name") = vbOK Then
GoTo InputSheetName
End If
End If
Else
If MsgBox("Invalid date string.", _
vbCritical + vbOKCancel, "Hi date value") = vbOK Then
GoTo InputHiDate
End If
End If
Else
If MsgBox("Invalid date string.", _
vbCritical + vbOKCancel, "Low date value") = vbOK Then
GoTo InputLowDate
End If
End If

End Sub




'---------------------8<----------------------------

Regards,
John



Ο χÏ�ήστης "Chris Van Nuys" Î*γγÏ�αψε:

Hi -- Try this, it should do the trick. Nothing fancy in here.

This code will copy all data in between rows B through DD the "lowDate" and
"highDate" that you specify. It assumes your dates are in Row A.

Let me know if you have any questions or need any help.

You need to change the "lowDate" and "highDate" below to match whatever
dates you want it to look between.

I hope this helps, and I hope I've been helpful. Please let me know.

Sub copy_data()
Dim lowDate As String
Dim highDate As String
Dim row As Integer
Dim skiprow As Integer

LastRow = Cells.Find("*", SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).row

lowDate = "1/9/2007"
highDate = "6/1/2007"
row = 0
skiprow = 0

Range("A1:A" & LastRow).Select

For Each Cell In Selection

If Cell.Value < lowDate Then
skiprow = Cell.row + 1
End If

If Cell.Value lowDate And Cell.Value < highDate Then
row = Cell.row
End If

Next

Range("B" & skiprow & ":DD" & row).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Range("A1").Select

End Sub


--
Best of Luck!,

Chris Van Nuys

Become more effective and efficient: Drive your business career by
mastering Excel!


" wrote:

hey everyone

I have date/time in colunm A and data through DD and I would like some
code that would find all the rows that are between two dates/times and
compy them into a different sheet.

Any help would be awesome

Thanks

Find if between dates
 

Chris,

Great stuff!! I changed low date and high date to date rather then
string and its working awesome.
Only one problem, if there are no rows with the date then it doesn't
work. Is there anyway to stop this?

What I want to do with this now is the following:

If there are is data that fall in the dates then send an email if not
end sub without error and run the next macro.

Thanks so much for the help