• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Move Cells from one sheet to another


New Member
I am creating a spreadsheet that will allow users to update a "notes" cell with their comments. I want the main worksheet to keep only the latest 3 cells of data. These cells are in descending order, as a part of the same column. Whenever a new comment is added it should move the oldest to a second worksheet.

The second worksheet would keep all of the archived notes. Is there a formula or set of formulas I could use to accomplish this task?


Excel Ninja
Staff member

Can you please tell us where the range of Comments is and are there dates associated in adjacent cells

ie: Date A10:A13 Comments B10:B13 etc


New Member
You're pretty much spot on. The comments will be in cells A10:A13. I'm using a formula to create a timestamp in cells B10:B13.


Excel Ninja
Staff member

Try the code below which must be pasted into a Codesheet Object of "Sheet1" in VBA

Adjust Source and Dest references as appropriate

Ente a comment in A13 and Date in B13

When the B13 changes the macro copies the new line to the Dest Sheet and then shifts the data up on the Source sheet

You may want to add a bit of code to sort the comments


Private Sub Worksheet_Change(ByVal Target As Range)

Source = "From" 'Name of Source worksheet

Dest = "to" 'Name of Destination worksheet

If Target.Address = "$B$13" Then



ActiveWorkbook.Sheets(Dest).Range("A2").Insert Shift:=xlDown





End If

End Sub