VBA - to paste between worksheets depending on cell values

smashthegas

New Member
Joined
Sep 10, 2015
Messages
4
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
ABCDUVW
1SurnameForenameDOBJMBConcluded
2SmithTom01/01/1960Yes
3SmithTom01/01/196001/01/2019
4SmithTom01/01/196002/03/2019
5SmithTom01/01/196005/05/2016
6SmithTom01/01/1960Yes
7SmithTom01/01/1960Yes

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Live Cases
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,446
Office Version
  1. 2013
Platform
  1. Windows
Code:
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

New Member
Joined
Sep 10, 2015
Messages
4
Thanks so much JLGWhiz! I had some of it right but you have done it perfectly! Sincerest appreciation :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,276
Messages
5,527,732
Members
409,786
Latest member
AbdulMoix

This Week's Hot Topics

Top