I have a simple spreadsheet in Excel 2013 of a workplan in sheet 1. In Column H, you can choose from a drop down menu who needs to be working on certain tasks.
Because the list of activities is long, I want to copy the whole row to Sheet 2 whenever SM is chosen in column H.
I have never worked with VBA code and tried copying it from forums and putting in my variables but it's not working.
Sub MoveClosed()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("Sheet 1").Cells(Rows.Count, "H").End(xlUp).Row
lr2 = Sheets("Sheet 2").Cells(Rows.Count, "H").End(xlUp).Row
Sheets("Sheet 1").Activate
For r = 2 To lr
If Range("H" & r).Value = "SM" Then
Rows(r).Copy
Sheets("Sheet 2").Rows(lr2 + 1).PasteSpecial Paste:=xlPasteValues
End If
Rows(r).Delete
Next r
End Sub
Because the list of activities is long, I want to copy the whole row to Sheet 2 whenever SM is chosen in column H.
I have never worked with VBA code and tried copying it from forums and putting in my variables but it's not working.
Sub MoveClosed()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("Sheet 1").Cells(Rows.Count, "H").End(xlUp).Row
lr2 = Sheets("Sheet 2").Cells(Rows.Count, "H").End(xlUp).Row
Sheets("Sheet 1").Activate
For r = 2 To lr
If Range("H" & r).Value = "SM" Then
Rows(r).Copy
Sheets("Sheet 2").Rows(lr2 + 1).PasteSpecial Paste:=xlPasteValues
End If
Rows(r).Delete
Next r
End Sub