Excel Macro to Move Completed Rows into Another Sheet

[papa]

It took me good 40 minutes to figure this one out.

To save “soul-searching” time in the future, here is a complete listing:

Sub Archive_Completed()

' Archive_Complete Macro

' 2012-02-15 WE  17:21  Remove complete lines from INPUT sheet and

'                       move them to "Historical Staffing" sheet

'Make sure it is INPUT sheet

Sheets("INPUT").Select

'Turn OFF calculation and screen updating to speed up the macro

With Application

.Calculation = xlCalculationManual

.ScreenUpdating = False

End With

'Select first data row

Range("A4").Select

'Go through all rows until an empty row reached

Do Until IsEmpty(ActiveCell)

'Move to column 8 - "Completed"

'Note columns are shifted - fix logic here

ActiveCell.Offset(0, .Select

'Check if value in 8-th column is Yes

If ActiveCell.Value = "Yes" Then

'Cut entire row

Selection.EntireRow.Cut

'Go to Historical Staffing sheet

Sheets("Historical Staffing").Select

'Go to the bottom on that sheet

Range("A1").End(xlDown).Select

ActiveCell.Offset(1, 0).Select

'Insert row of data

Selection.Insert Shift:=xlDown

'Return back to INPUT sheet

Sheets("INPUT").Select

'Delete an empty row

Selection.EntireRow.Delete Shift:=xlUp

'Note: Delete moves selection one row down

'Just return to first column

ActiveCell.Offset(0, -.Select

Else

'Move one row down to the first column

ActiveCell.Offset(1, -.Select

End If

Loop

'Turn ON calculation and screen updating

With Application

.Calculation = xlCalculationAutomatic

.ScreenUpdating = True

End With

End Sub

Each line of code has a comment. So, “No further question, your honor…”

[Author]

Posts