I actually had more columns of text, but I wanted to simplify the problem
that I'm having so I see how the code works and manipulate it.

"Bernard Liengme" wrote:

The With Cell / End With seems unnecessary
look at my suggested code and see if it works for you.
I thought you just had column A with time and B with text
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bruce S" wrote in message
...
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