I have a working code (for the most part).
I have 2 issues with this code which I can't figure out how to get past.
Purpose of VBA
Workbook contains 2 sheets, the first is named Prospects the second is named Closed
When the command button is clicked, the entire Prospects Sheet should be scanned for the value of "Closed" in column P.
Any row that has "Closed" in column P should be moved into the first available empty row(s) in the Closed Sheet
Any row that is removed from the Prospect Sheet needs to be deleted and shifted up so that there are no empty rows between data and all formatting remains
When the "Closed" row is moved to the Closed Sheet I lose the formatting in that row on the Prospects Sheet. I have Data Validation set in certain columns on the Prospects Sheet and that's gone once the VBA runs.
The code seems to only work for one row at a time. I'd like the code to grab all of the rows that are marked as "Closed" in column P on the Prospects Sheet, following the same criteria as explained above.
Thank you in advance,
- Private Sub CommandButton1_Click()
- Dim Check As Range, r As Long, lastrow2 As Long, LastRow As Long
- Application.ScreenUpdating = False
- LastRow = Worksheets("Prospects").UsedRange.Rows.Count
- For r = LastRow To 2 Step -1
- If Worksheets("Prospects").Range("p" & r).Value = "Closed" Then
- Worksheets("Prospects").Rows(r).Cut Destination:=Worksheets("Closed").Range("A" & Rows.Count).End(xlUp)(2)
- End If
- Next r
- Application.ScreenUpdating = True
- End Sub