Copy, paste and delete rows in vba

[postleneo]

Hi guys I'm hoping if anyone could provide me assistance. I have a table in worksheet 1 with data in rows 3 to 1000 and columns A to P. If column P holds a date for any row I need to copy the full row, paste the row to the next available row in worksheet 2 and then delete the appropriate rows from worksheet 1. I have a basic understanding of vba and any help to achieve this would be appreciated

 

[DanteAmor]

Try:

Sub Copy_delete()
    'Copy, paste and delete rows in vba
    Dim uf As Long, i As Long, datos As Range, r As Range, fech As Range
    Dim h1 As Worksheet, h2 As Worksheet
    '
    Application.ScreenUpdating = False
    Set h1 = Sheets("sheet1")
    Set h2 = Sheets("sheet2")
    '
    uf = h1.Range("P" & Rows.Count).End(xlUp).Row
    Set datos = h1.Range("P3:P" & uf)
    For Each fech In datos
        If IsDate(fech.Value) Then
            If r Is Nothing Then
                Set r = h1.Range("P" & fech.Row)
            Else
                Set r = Union(r, h1.Range("P" & fech.Row))
            End If
        End If
    Next
    If Not r Is Nothing Then
        uf = h2.Range("P" & Rows.Count).End(xlUp).Row + 1
        r.EntireRow.Copy h2.Range("A" & uf)
        r.EntireRow.Delete
        Set r = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

 

[postleneo]

Works a treat.... much appreciated many many thanks

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[postleneo]

Just another issue.... If there is no date in column P but a value N/K.... How would I change the code to copy paste and delete the rows for either entry?

 

[DanteAmor]

Change this:

[COLOR=#333333]If IsDate(fech.Value) Then[/COLOR]

By:

[COLOR=#333333]If fech.Value <> "" Then[/COLOR]