Hi Folks
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..
Kind regards
DezB
Code
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
Display More