Copy Row to Sheet If Column Value is between time frame
 

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

Copy Row to Sheet If Column Value is between time frame
 

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

Copy Row to Sheet If Column Value is between time frame
 

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

Copy Row to Sheet If Column Value is between time frame
 

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

Copy Row to Sheet If Column Value is between time frame
 

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

Copy Row to Sheet If Column Value is between time frame
 

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

Copy Row to Sheet If Column Value is between time frame
 

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

Copy Row to Sheet If Column Value is between time frame
 

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

Copy Row to Sheet If Column Value is between time frame
 

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