Discussion Locked

Excel auto cutting pasting to wrksht

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In Excel, I would like to create some type of code that automatically cuts a row of data from one worksheet, "Open Records", to another worksheet, "Closed Records", in the same work book.

For example, a record is entered with a corresponding date. Possibly a week later or more, additional data is entered and a closing date is entered.

I would like to automate that when the closing date is entered, the row is automatically cut and pasted to a "Completed Worksheet".

Please Help! Thank You!

3 replies

by it_cowboy

You will not be able to set an event to trigger when you enter data into a cell, but you can trigger events when you open or close a workbook, or click on a button.

Perhaps on closing the workbook you could get it to move the data then save and close the workbook.

Create a Sub called Auto_Close, then enter your macro information into the Subroutine. It will execute the code when the workbook is closed. You may want to set a warning to alert you if there are rows to be cut and paste, just count up the rows and program a message box to appear...

Sub auto_Close()

Dim X As Integer
Dim intCloseDateCol as Integer 'Column Number for the Closing Date
Dim intEndOfData as Integer 'Last Row of Data
Sheets("Sheet1").Select
For X = 1 To intEndOfData
If Cells(X, intCloseDateCol) > 0 Then
Rows(X).Select
Selection.Cut
Sheets("Sheet2").Select
Rows(Y).Select
ActiveSheet.Paste
End If Next

End Sub

If you want to CUT the data, you may have to experiment a bit as the row counter will be at the next line, however, everything has moved up one. Maybe start from the top each time the routine finds a row that needs to be moved.

Good Luck

Greg J

This discussion is closed to new replies.