This is the macro that I have so far. I can't get the Mytime to be set to a
range

Sub test_find_copy_paste()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim AllCells As Range, Cell As Range
Dim n As Long
Dim Mytime

Application.ScreenUpdating = False

Set ws2 = Sheets("Overnight")
Set ws1 = Sheets("Automation")
*** Mytime = TimeValue("1:00" to "5:00")*** NOT WORKING

Set AllCells = Sheets("Automation").Range("A1", Range("G65536").End(xlUp))
n = Sheets("Overnight").Range("A" & Rows.Count).End(xlUp).Row + 1
For Each Cell In AllCells
With Cell
If Cell = Mytime Then
Cell.EntireRow.Copy Destination:=ws2.Cells(n, 1)
n = n + 1
End If
End With
Next Cell
Set ws1 = Nothing
Set ws2 = Nothing
Set AllCells = Nothing

Application.ScreenUpdating = True
End Sub

"Bruce S" wrote:

I'm looking for a formula or macro that will allow me to copy rows to an
existing sheet based upon a time range in one column.

For example

Sheet 1 would have two columns (A,B)
ColumnA has times listed in military time (1:00, 13:00, 17:00)
ColumnB has text associated with the time

If Column A is within a specific time range, copy the row(s) to Sheet2