Problems with sheet copying/ deleting formula

[Darkcloud617]

Hello,


Thank you to anyone in advance for any help with my issue. I am newer to excel macros and I am a bit mixed up with some VBA and cant seem to get it to do what I need. I have stitched this code together from forums... because I just cannot figure it out otherwise. I am needing two macros that are very similar to each other.


Macro 1 (example below): If the word 'Move' is typed into Column Q and the button (macro) is hit on the 'Projects' page then excel will erase data out of the specific cells but leave the row intact (not 'deleting the row') and paste into the next blank row on a sheet called 'Pending'. I have included a screenshot of the 'Projects' page. The data starts on rows 5 in 'Projects' and row 3 on all others. All of the cells need erased in the screenshot. From A to S. A. B and E are drop downs.

Macro 2: I am needing a separate macro that is very similar to the above. It would still follow its process on the 'Projects' main page but copy the row to a sheet called 'Tracking' instead of 'Pending'. The main difference is that you type 'Move' in column R, hit the button, and it searches other sheets in the workbook (3 sheets except tracking and projects) for all instances of the row and deletes them (deletes the entire row in this case) . This would, in theory, 'Close' the file out by deleting all rows on other sheets and erasing the data from the 'Projects' page. Leaving the file for Tracking purposes only.


I am at a loss. I have built this spreadsheet... learning VBA as I have been going along pretty much... and this is the last code to make it actually work so any notes would be greatly appreciated.


In general: Both of the above will just be used for making specific files (or rows) in 'Projects' 'Pending' or 'Close' and move or delete them appropriately. I really wanted to give all information possible so apologies if the above is wordy. Thank you again for any help you can give.


Here is what I came up with for Macro 1 which deletes the rows and is slow:

Sub RoundedRectangle4_Click()



Dim xRg As Range[IMG]https://ibb.co/d5hBYn[/IMG]
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Projects").UsedRange.Rows.Count
J = Worksheets("Pending").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Pending").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Projects").Range("Q1:Q" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Move" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Pending").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "Move" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub