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

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

Sub moveit()
starthour = InputBox("Give starting hour")
starthour = starthour / 24
endhour = InputBox("Give ending hour")
endhour = endhour / 24
j = 1
With Worksheets("Sheet1")
For Each mycell In .Range("A1:A100")
If mycell = starthour And mycell <= endhour Then
Worksheets("Sheet2").Cells(j, "A") = mycell
Worksheets("Sheet2").Cells(j, "B") = mycell.Offset(columnOffset:=1)
j = j + 1
End If
Next
End With
End Sub


You will need to format column A of Sheet2 to display time
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

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

Second offering

Sub moveit2()
Set wks2 = Worksheets("Sheet2")
starthour = InputBox("Give starting hour")
starthour = starthour / 24
endhour = InputBox("Give ending hour")
endhour = endhour / 24
lastcell = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
j = 1
With Worksheets("Sheet1")
For k = 1 To lastcell
If Cells(k, 1) = starthour And mycell <= endhour Then
Cells(k, 1).EntireRow.Copy Destination:=wks2.Cells(j, 1)
j = j + 1
End If
Next
End With
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

I recreated a basic spreasheet upon what I described and when I run the macro
on the spreadsheet, I get the following error

Compile error: Varible not defined

Its highlighting starthour = InputBox("Give starting hour")

"Bernard Liengme" wrote:

Sub moveit()
starthour = InputBox("Give starting hour")
starthour = starthour / 24
endhour = InputBox("Give ending hour")
endhour = endhour / 24
j = 1
With Worksheets("Sheet1")
For Each mycell In .Range("A1:A100")
If mycell = starthour And mycell <= endhour Then
Worksheets("Sheet2").Cells(j, "A") = mycell
Worksheets("Sheet2").Cells(j, "B") = mycell.Offset(columnOffset:=1)
j = j + 1
End If
Next
End With
End Sub


You will need to format column A of Sheet2 to display time
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

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

Unfortunately, I'm still getting that same error. Anyway, thanks for your
assistance.

"Bernard Liengme" wrote:

Second offering

Sub moveit2()
Set wks2 = Worksheets("Sheet2")
starthour = InputBox("Give starting hour")
starthour = starthour / 24
endhour = InputBox("Give ending hour")
endhour = endhour / 24
lastcell = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
j = 1
With Worksheets("Sheet1")
For k = 1 To lastcell
If Cells(k, 1) = starthour And mycell <= endhour Then
Cells(k, 1).EntireRow.Copy Destination:=wks2.Cells(j, 1)
j = j + 1
End If
Next
End With
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

Look through the code and add a Dim statement fro every variable
Sub moveit2()
Dim wks as Worksheet
Dim starthour, endhour, lastcell, j, k

I think I got them all
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bruce S" wrote in message
...
Unfortunately, I'm still getting that same error. Anyway, thanks for your
assistance.

"Bernard Liengme" wrote:

Second offering

Sub moveit2()
Set wks2 = Worksheets("Sheet2")
starthour = InputBox("Give starting hour")
starthour = starthour / 24
endhour = InputBox("Give ending hour")
endhour = endhour / 24
lastcell = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
j = 1
With Worksheets("Sheet1")
For k = 1 To lastcell
If Cells(k, 1) = starthour And mycell <= endhour Then
Cells(k, 1).EntireRow.Copy Destination:=wks2.Cells(j, 1)
j = j + 1
End If
Next
End With
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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