This topic contains 0 replies, has 1 voice, and was last updated by papa 12 years, 2 months ago.
Viewing 1 post (of 1 total)
Viewing 1 post (of 1 total)
You must be logged in to reply to this topic.
Home › Forums › Tech › Office and Applications › Excel Macro to Move Completed Rows into Another Sheet
This topic contains 0 replies, has 1 voice, and was last updated by papa 12 years, 2 months ago.
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…”
You must be logged in to reply to this topic.
Posted | Modified |
---|---|
Nov 16 | Dec 2 - 14:52 |
2017-11 - From the Editor - Who is My President?... | |
Nov 29 | Nov 26 - 14:21 |
Sat *WARREN* 09:00 - - - Sun *BROWN* 09:00... | |
Nov 26 | Nov 26 - 14:19 |
19 Game Results - 2017-11-26 SU - Diego dropped others - Andres scored 4... | |
Nov 24 | Nov 24 - 18:10 |
Adventures in the Dominican... | |
Oct 11 | Nov 17 - 20:32 |
Home... |
Rani S | Nov 15 |
Nice lesson, thank you for sharing with us :)... | |
Scottgal | Nov 4 |
Всем привет, уже третий день не могу поставить D... | |
useaglefcu.org | Jul 4 |
In MongoDB, is it possible to update the value of ... | |
Andre | Jun 24 |
Works with Server 2016 IIS 10.... |
|
Copyright © 2024
Domain Web Center . All Rights Reserved. Magazine Basic theme designed by Themes by bavotasan.com . Disclaimer | Powered by WordPress and bbPress. | Contact the Editor |