Pasting to last row in a table

[Sarche]

Hey guys! Below is a macro I have been working on, but it still does not function quite how I want it to. I have a workbook with sheet1 "Schedule" and sheet2 "Complete". I need for when I put a "99" in column P of the table on the Schedule sheet it will cut and paste the entire row to the last row in the table on the Compete Sheet. Then delete the now blank row on the Schedule sheet. My problem with my code is that it will cut the rows with 99 in column P, but it will paste it on the first blank for AFTER the table on the Complete sheet instead of IN the table. I also can't seem to get it to delete the now blank rows. Please Help!!

Sub Completecutpaste()
    Dim p As Variant
    Dim endrow As Integer
    Dim Schedule As Worksheet, Complete As Worksheet
    Dim lastRow As Long
    
    
    Set Schedule = ActiveWorkbook.Sheets("Schedule")
    Set Complete = ActiveWorkbook.Sheets("Complete")


    endrow = Schedule.Range("A" & Schedule.Rows.Count).End(xlUp).Row
    lastRow = Complete.Cells(Complete.Rows.Count, "A").End(xlUp).Row '+ 1 for next available row


    For p = 2 To endrow
        If Schedule.Cells(p, "P").Value = "99" Then
           Schedule.Cells(p, "P").EntireRow.Cut Destination:=Complete.Range("A" & Complete.Rows.Count).End(xlUp).Offset(1)


        End If


    Next
End Sub

 

[mrmmickle1]

Sarche,

Welcome to the board!

You need to reference the table object otherwise a new row will not be added to your table but the worksheet instead

code....

[COLOR=#0000ff]Sub[/COLOR] Completecutpaste()

 [COLOR=#0000ff]   Dim[/COLOR] p [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] endrow[COLOR=#0000ff] As Integer[/COLOR]
[COLOR=#0000ff]    Dim [/COLOR]Schedule [COLOR=#0000ff]As[/COLOR] Worksheet, Complete [COLOR=#0000ff]As [/COLOR]Worksheet
  [COLOR=#0000ff]  Dim [/COLOR]oLastRow [COLOR=#0000ff]As [/COLOR]ListRow
    
 [COLOR=#0000ff]   Set [/COLOR]Schedule = ActiveWorkbook.Sheets("Schedule")
   [COLOR=#0000ff] Set[/COLOR] Complete = ActiveWorkbook.Sheets("Complete")

    endrow = Schedule.Range("A" & Schedule.Rows.Count).End(xlUp).Row

[COLOR=#0000ff]    For[/COLOR] p = 2 [COLOR=#0000ff]To[/COLOR] endrow

[COLOR=#0000ff]        If [/COLOR]Schedule.Cells(p, "P").Value = "99" [COLOR=#0000ff]Then[/COLOR]
        [COLOR=#0000ff]   Set[/COLOR] oLastRow = Worksheets("Complete").ListObjects("Table1").ListRows.Add
           Schedule.Cells(p, "P").EntireRow.Copy
           oLastRow.Range.PasteSpecial xlPasteValues
           Schedule.Cells(p, "P").EntireRow.Delete
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]    Next[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[Sarche]

That worked amazingly!! Thank you so much for your help Mrmmickle.

 

[mrmmickle1]

Your quite welcome. Glad I could offer some assistance.