Hi,
I have 4 spreadsheets in my workbook called "master","1", "2", and "3". In
column K of the "master" sheet, the cell format is 'dd h:mm:ss'.
How do i write a macro that would go through that column....if it's less
than 5 hrs then move the row (from colum A to K) to sheet "1"...if it's
between 5hrs and 24 hrs then move that row to sheet "2" and if it's more than
24 hrs then move to sheet "3"...
the macro will keep on doing that until there is a blank line...

thanks.

With that format how are you managing to enter zero days without it decoming
text?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Don Doan" wrote in message
...
Hi,
I have 4 spreadsheets in my workbook called "master","1", "2", and "3".
In
column K of the "master" sheet, the cell format is 'dd h:mm:ss'.
How do i write a macro that would go through that column....if it's less
than 5 hrs then move the row (from colum A to K) to sheet "1"...if it's
between 5hrs and 24 hrs then move that row to sheet "2" and if it's more
than
24 hrs then move to sheet "3"...
the macro will keep on doing that until there is a blank line...

thanks.

Hi,
actually before column K, there are 2 other columns with the format
yyyy/mm/dd hh:mm:ss...
column K is the result of one column minus the other column

"Sandy Mann" wrote:

With that format how are you managing to enter zero days without it decoming
text?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Don Doan" wrote in message
...
Hi,
I have 4 spreadsheets in my workbook called "master","1", "2", and "3".
In
column K of the "master" sheet, the cell format is 'dd h:mm:ss'.
How do i write a macro that would go through that column....if it's less
than 5 hrs then move the row (from colum A to K) to sheet "1"...if it's
between 5hrs and 24 hrs then move that row to sheet "2" and if it's more
than
24 hrs then move to sheet "3"...
the macro will keep on doing that until there is a blank line...

thanks.

This code assumes that the data starts in Row 3:

Sub MoveIt2()
Dim LastRow As Long
Dim cell As Range
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim LastRow3 As Long
Dim cValue As Double

Sheets("Master").Activate

LastRow = Cells(Rows.Count, 11).End(xlUp).Row
LastRow1 = Sheets("1").Cells(Rows.Count, 11).End(xlUp).Row + 1
LastRow2 = Sheets("2").Cells(Rows.Count, 11).End(xlUp).Row + 1
LastRow3 = Sheets("3").Cells(Rows.Count, 11).End(xlUp).Row + 1

For Each cell In Range(Cells(3, 11), Cells(LastRow, 11))
If cell.Value < "" Then

cValue = cell.Value

Select Case cValue

Case Is < 5 / 24

Range(Cells(cell.Row, 1), Cells(cell.Row, 11)).Copy
Destination:= _
Sheets("1").Cells(LastRow1, 1)
LastRow1 = LastRow1 + 1
GoTo GetOut
Case Is <= 1
Range(Cells(cell.Row, 1), Cells(cell.Row, 11)).Copy
Destination:= _
Sheets("2").Cells(LastRow2, 1)
LastRow2 = LastRow2 + 1
GoTo GetOut
Case Else
Range(Cells(cell.Row, 1), Cells(cell.Row, 11)).Copy
Destination:= _
Sheets("3").Cells(LastRow3, 1)
LastRow3 = LastRow3 + 1
GetOut:
End Select
End If
Next cell

End Sub


If you want to remove the data as well then clear the constants after the
copying has been done.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Don Doan" wrote in message
...
Hi,
actually before column K, there are 2 other columns with the format
yyyy/mm/dd hh:mm:ss...
column K is the result of one column minus the other column

"Sandy Mann" wrote:

With that format how are you managing to enter zero days without it
decoming
text?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Don Doan" wrote in message
...
Hi,
I have 4 spreadsheets in my workbook called "master","1", "2", and "3".
In
column K of the "master" sheet, the cell format is 'dd h:mm:ss'.
How do i write a macro that would go through that column....if it's
less
than 5 hrs then move the row (from colum A to K) to sheet "1"...if it's
between 5hrs and 24 hrs then move that row to sheet "2" and if it's
more
than
24 hrs then move to sheet "3"...
the macro will keep on doing that until there is a blank line...

thanks.