VBA is not my strong point, but I'm trying set something up where if the word "Yes" is selected in a particular cell (col P) then, when the macro is run, it takes the contents of that row from Column A through to P and moves those contents to the next available row on another sheet.
The caveat is that multiple rows may have Yes selected before the macro is run. In that case it needs to go through them one by one and move each of the selected rows to the other sheet.
The code below seems to work to a point but only for one selection at a time. In addition it also overwrites what has also already been moved to the other sheet.
Any advice or help please, I'm well and truly stuck..
Sub ArchiveData() 'Created 03-06-21 Dim xRg As Range Dim xCell As Range Dim I As Long Dim J As Long Dim K As Long I = Worksheets("Active_Cases").UsedRange.Rows.Count J = Worksheets("Archived_Cases").UsedRange.Rows.Count If J = 1 Then If Application.WorksheetFunction.CountA(Worksheets("Archived_Cases").UsedRange) = 0 Then J = 0 End If Set xRg = Worksheets("Active_Cases").Range("P1:P" & I) On Error Resume Next Application.ScreenUpdating = False For K = 1 To xRg.Count If CStr(xRg(K).Value) = "Yes" Then xRg(K).EntireRow.Copy Destination:=Worksheets("Archived_Cases").Range("A" & J + 0) xRg(K).EntireRow.Delete If CStr(xRg(K).Value) = "Done" Then K = K - 1 End If J = J + 1 End If Next Application.ScreenUpdating = True End Sub