Thread: Cut, Paste to other Sheet, Delete Macro

First off, new to macros but have had success with recording a few and using a few from this forum. Now for what I am trying to accomplish and hope somebody can help. I have a sheet titled "Floor Plan". Within "Floor Plan", Column "K" will contain currency formatting until the date it is paid at which time an input person will change the cell to "Paid xx/xx/xx". I would like to create a macro that we could choose when to initiate it, beginning on row 9, searching for any cell in column "K" that contains the word "Paid" or "PAID" and cut the entire row, paste it to the next available row in a sheet named "Paid TRs". I would then like the blank rows on "Floor Plan" to be deleted and (xlUP[?]). A button to run this would be nice but a keyboard shortcut will also work.

This is my first time posting so please forgive me if my jargon is insufficient!

I would use Excel's AutoFilter for this. This is easy to construct when looking at your real layout with a reasonable amount of sample data in it.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

This should do it:

Option Explicit

Sub TransferPAID()
'Jerry Beaucaire   8/17/2011

Dim LR As Long

With Sheets("Floor Plan")
    .AutoFilterMode = False
    .Rows("8:8").AutoFilter
    .Rows("8:8").AutoFilter Field:=11, Criteria1:="=Paid*"
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    If LR > 8 Then
        .Range("A9:A" & LR).EntireRow.Copy Sheets("Paid TRs").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Range("A9:A" & LR).SpecialCells(xlVisible).EntireRow.Delete xlShiftUp
    End If
    .AutoFilterMode = False
End With

End Sub

Try changing this one line:

                 .Range("A9:A" & LR).EntireRow.Delete xlShiftUp

For your insert row, this should do it:

Sub InsertRow()
'
' InsertRow Macro
' Keyboard Shortcut: Ctrl+Shift+I
'
    Sheets("Macro Sheet").Range("A18:U18").Copy _
        Sheets("Floor Plan").Range("F" & Rows.Count).End(xlUp).Offset(1, -5)

End Sub