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 (E-Mail Removed)> wrote in message
news:8124A754-447F-4849-BAE7-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:4608DAE0-01EA-4E54-86ED-(E-Mail Removed)...
> 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 (E-Mail Removed)> wrote in message
news:8124A754-447F-4849-BAE7-(E-Mail Removed)...
> 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 (E-Mail Removed)> wrote in message
> news:8124A754-447F-4849-BAE7-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:4608DAE0-01EA-4E54-86ED-(E-Mail Removed)...
> > 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 (E-Mail Removed)> wrote in message
> news:8124A754-447F-4849-BAE7-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news73975B8-1958-4C3F-AC9F-(E-Mail Removed)...
> 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 (E-Mail Removed)> wrote in message
>> news:8124A754-447F-4849-BAE7-(E-Mail Removed)...
>> > 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
>>
>>
>>