Dear Friends, I would like have a formula which helps me save the previous comments of the cell (Say A1)automatically to another cell on same sheet or another sheet upon entering new data in the same cell (A1) this required because everyday new data is entered in the cell if it saved I would have the previous history

Hi, I have assumed that when you want to save comments you mean a comments text box linked to a cell, and not the contents of the cell itself. You asked if the comments could be saved automatically when entering new data in the same cell. As the comments are likely to be added after the contents of the cell changes, then running the save macro when the cell contents change will not work. If however you meant that you want the contents of the same cell saved each day, then this can be done when the contents change. As I have assumed that you want to save the linked comments I suggest having a button on the sheet, which runs the macro. For this example there are two worksheets in the workbook One named "Source" and the other named "SavedComments" The Contents of the Comments attached to cell A1 on the 'Source' worksheet will be saved to a cell on the 'SavedComments' worksheet. As you said that there is a new comment every day, I have used the day and month as the index to the cell where each day's comments will be saved. (At the end of the year, change the name of the "SavedComments" worksheet to say, "Comments2009" and add a new "SavedComments" worksheet. Use the Control Toolbox toolbar to create a command button on the 'Source' worksheet and add the code to it (In Design mode, right click button and select view code). Enter 'Option explicit before the opening 'Private Sub CommandButton1_Click()' and enter the code, but excluding the opening sub and the 'End Sub' as these are already in place for the button. Option Explicit Private Sub CommandButton1_Click() 'save comments from cell A1 and store in a new cell each day Dim strComms As String On Error GoTo ErrHnd 'copy and save the comments With ActiveWorkbook 'get comments from cell A1 strComms = .Worksheets("Source").Range("A1").Comment.Shape.AlternativeText 'remove "Text box: " from start of comment strComms = Right(strComms, Len(strComms) - 10) 'save comments .Worksheets("SavedComments").Cells(Day(Now()), Month(Now())).Value = strComms 'Remove the comments .Worksheets("Source").Range("A1").Comment.Delete 'save the workbook .Save End With Exit Sub 'error handler ErrHnd: Err.Clear End Sub This code also deletes the comment and saves the workbook. If you don't want the comment erased, just remove the line, or comment it out with ' HTH Regards