• 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

    Hui...

  • 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

H

Hellcat94

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?
 
Hellcat


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
 
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.
 
Hellct


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


Sheets(Source).Range("A13:B13").Activate

Selection.Copy


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


Sheets(Source).Range("A11:B13").Copy

Range("A10").Activate

ActiveSheet.Paste


Range("A13:B13").ClearContents


End If


End Sub
 
You must log in or register to reply here.
Back
Top