long time excel user but new to marcos

[jasondabsher]

i am just looking for a simple macro that will let me move rows from a sheet in a workbook called Current Work to rows into a sheet called Past Work when the column F which is the "completed" column has a "yes" in it

 

[Yongle]

welcome to the forum

one way
Last entry in Column A used to determine last row in both sheets

Sub MoveRows()
    Dim Current As Worksheet, Past As Variant, r As Long, x As Long
    Set Current = Sheets("Current Work")
    Set Past = Sheets("Past Work")
    Set Past = Past.Cells(Past.Rows.Count, "A")
    x = Current.Cells(Current.Rows.Count, "A").End(xlUp).Row
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For r = x To 2 Step -1
        If LCase(Current.Cells(r, "F").Value) = "yes" Then
            Current.Cells(r, "A").EntireRow.Copy Destination:=Past.End(xlUp).Offset(1)
            Current.Cells(r, "A").EntireRow.Delete
        End If
    Next r
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[jasondabsher]

okay i have the macro created correctly i think.

https://puu.sh/G5EyX.png

now how do i add it into the sheet and where does it need to be you said? at the bottom of column A confuses me a little. you mean at the end of each row?

 

[Yongle]

at the bottom of column A confuses me a little. you mean at the end of each row?

Like I said the last entry in column A determines last row
- VBA needs to be told how to work out which row is the last row
- usually the last value in column A is on the last row in the data
- if that is not the case, which column always has a value in the last row?

Test on a COPY of your workbook

To Run
{ALT}{F8} \ click on MoveRows \ Run

 

[jasondabsher]

okay so it will have to be run also. there is no way for it to do it automatically when yes it put into the box. thanks for your help

 

[Joe4]

okay so it will have to be run also. there is no way for it to do it automatically when yes it put into the box. thanks for your help

If "yes" is being selected/typed in the cell manually, then you can use a "Worksheet_Change" event procedure to run the code automatically.
Event Procedures can automatically run VBA code upon some "event" happening (like the manual update of a cell, the selection of a cell, the opening of a workbook, the saving of a file, etc).

So, do the following:
1. Go to your "Current Work" sheet
2. Right-click on the Sheet tab name at the bottom of the screen
3. Select "View Code"
4. Paste the following code in the resulting VB Editor Window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cell As Range
    
'   Look for changes in column F, if none, exit sub
    Set rng = Intersect(Target, Columns("F:F"))
    If rng Is Nothing Then Exit Sub
    
'   Loop through values in column F just upated
    For Each cell In rng
        If LCase(cell) = "yes" Then
            cell.EntireRow.Copy Destination:=Sheets("Past Work").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Application.EnableEvents = False
            cell.EntireRow.Delete
            Application.EnableEvents = True
        End If
    Next cell

End Sub

In order for the code to work automatically, it MUST be placed in this sheet module, and it MUST have this exact name.

 

[jasondabsher]

awesome that all worked great. one last thing. is there a way to make it timestamp it when it moves it?

 

[Yongle]

Delete previous procedure and replace with the one below and replace Z with the correct column letter in this line
Cells(cell.Row, "Z") = Now

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, cell As Range
   
'   Look for changes in column F, if none, exit sub
    Set rng = Intersect(Target, Range("F2:F" & Rows.Count))
    If rng Is Nothing Then Exit Sub
   
'   Loop through values in column F just upated
    For Each cell In rng
        If LCase(cell) = "yes" Then
            Application.EnableEvents = False
            Cells(cell.Row, "Z") = Now
            cell.EntireRow.Copy Destination:=Sheets("Past Work").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            cell.EntireRow.Delete
            Application.EnableEvents = True
        End If
    Next cell
End Sub