VBA - to paste between worksheets depending on cell values

[smashthegas]

Hi everyone,


I am looking for some assistance please with some VBA to paste between worksheets. I have a workbook with three tabs all containing the same headers and I am looking for VBA to help paste rows from the first tab to the second or third when there is a certain value in columns V or W of the front tab - "live cases"


If the word "Yes" is entered in column V, then when the macro is run I want it to paste the entire row to sheet 3 "JMB" on the next available (blank) row, or if there is a date entered in column W I want it to do the same onto the "concluded" sheet, sheet 2. I would then like the entire row to delete itself from the "Active Cases" sheet.


I have played around an managed to do bits and pieces but I am still learning after many years and am still only on the basics. I have attached a screenshot of the first sheet - the other 2 sheets are the same, which is where the pastes will go.


Thank you so much

Excel 2013/2016

	A	B	C	D	U	V	W
Smith	Tom	01/01/1960			Yes
Smith	Tom	01/01/1960				01/01/2019
Smith	Tom	01/01/1960				02/03/2019
Smith	Tom	01/01/1960				05/05/2016
Smith	Tom	01/01/1960			Yes
Smith	Tom	01/01/1960			Yes

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]Surname[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]Forename[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]DOB[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=A6A6A6]#A6A6A6[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]JMB[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , align: center"]Concluded[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>

Live Cases​

 

[JLGWhiz]

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Set sh1 = Sheets("live cases")
Set sh2 = Sheets("JMB")
Set sh3 = Sheets("concluded")
sh1.UsedRange.AutoFilter 22, "Yes"
sh1.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
sh1.AutoFilterMode = False
sh1.UsedRange.AutoFilter 23, ">0"
sh1.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy sh3.Cells(Rows.Count, 1).End(xlUp)(2)
sh2.AutoFilterMode = False
End Sub

 

[smashthegas]

Thanks so much JLGWhiz! I had some of it right but you have done it perfectly! Sincerest appreciation

 

[JLGWhiz]

Thanks so much JLGWhiz! I had some of it right but you have done it perfectly! Sincerest appreciation

You're welcome,
regards, JLG