Excel Macro to Move Completed Rows into Another Sheet

Home Forums Tech Office and Applications Excel Macro to Move Completed Rows into Another Sheet

Tagged: , , , , ,

This topic contains 0 replies, has 1 voice, and was last updated by  papa 7 years, 9 months ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #2326

    papa
    Participant

    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, 8).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, -8).Select
    Else
    'Move one row down to the first column
    ActiveCell.Offset(1, -8).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…”

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.