Copy ROW from one spreadsheet to another

[alm395]

I have a report I am working on. One sheet named "BlueSkyDATA" has the raw data I am pulling from. It has 19 columns (A-S) and hundreds of rows. If the time data in column K is greater than or equal to 1 hour, I would like that whole row to be copied to another spreadsheet in the same workbook, called "Dispatched" beginning at A4. How can I do this?

Thanks so much, in advance!
Ann

 

[My Aswer Is This]

Show us how this time data in column K will appear:

Will it Look like this 1:15

 

[alm395]

I have it currently displayed as 0:00:00.

 

[My Aswer Is This]

Try this:

Sub New_Filter()
'Modified 4-23-18 4:25 PM EDT
Application.ScreenUpdating = False
Sheets("BlueSkyDATA").Activate
    With ActiveSheet.Range(Cells(1, "K"), Cells(Cells(Rows.Count, "K").End(xlUp).Row, "K"))
        .AutoFilter Field:=1, Criteria1:=">=" & TimeValue("01:00:00")
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Dispatched").Range("A4")
        .AutoFilter
    End With
    
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

If you plan to run the script more then once. And you want to append the data to the sheet named Dispatched
You will need to us this script.
First time data will start on row (4) next time first row after last row

Sub New_Filter()
'Modified 4-23-18 4:50 PM EDT
Application.ScreenUpdating = False
Sheets("BlueSkyDATA").Activate
Dim Lastrow As Long
Lastrow = Sheets("Dispatched").Cells(Rows.Count, "K").End(xlUp).Row + 1
If Lastrow < 4 Then Lastrow = 4

    With ActiveSheet.Range(Cells(1, "K"), Cells(Cells(Rows.Count, "K").End(xlUp).Row, "K"))
        .AutoFilter Field:=1, Criteria1:=">=" & TimeValue("01:00:00")
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Dispatched").Cells(Lastrow, "A")
        .AutoFilter
    End With
    
Application.ScreenUpdating = True
End Sub

 

[alm395]

@My Answer is This
I had to tweak it a tiny bit, but it works!!! Thank you soo much! That just saved me so much time and a big 'ol headache! You are the BEST!!!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

@My Answer is This
I had to tweak it a tiny bit, but it works!!! Thank you soo much! That just saved me so much time and a big 'ol headache! You are the BEST!!!

 

[alm395]

I have another question...would it be possible to add to this same macro the same data in cells L (what we currently have) but add columns M, & N (the >= 1:00:00 timeframe), then copy those lines to the "Dispatched" spreadsheet, and do this each time the workbook is opened...then remove any duplicates that may occur on the "Dispatched" spreadsheet? The most important part of that is the copying the rows if L, M, & N are greater than or equal to the 1-hour mark. I hope that makes sense. Please advise.

 

[My Aswer Is This]

Now your wanting a lot more then your original post.
Now we need to see if L M and N are greater then.
And I'm not sure what you mean by duplicates.

You want to look in every single row and see if the row is a exact duplicate of any of the other rows.

That would mean looking in 19 columns on hundreds of rows to see if there are exact duplicates.
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[My Aswer Is This]

Try this:
For the first part of your new request.

Sub New_Filter()
'Modified 4-27-18 10:53 PM EDT
Application.ScreenUpdating = False
Sheets("BlueSkyDATA").Activate
Dim Lastrow As Long
Lastrow = Sheets("Dispatched").Cells(Rows.Count, "L").End(xlUp).Row + 1
If Lastrow < 4 Then Lastrow = 4

    With ActiveSheet.Range(Cells(1, "L").Resize(, 3), Cells(Cells(Rows.Count, "L").End(xlUp).Row, "L"))
        .AutoFilter Field:=1, Criteria1:=">=" & TimeValue("01:00:00")
         .AutoFilter Field:=2, Criteria1:=">=" & TimeValue("01:00:00")
          .AutoFilter Field:=3, Criteria1:=">=" & TimeValue("01:00:00")
         
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Dispatched").Cells(Lastrow, "A")
        .AutoFilter
    End With
    
Application.ScreenUpdating = True
End Sub