Save cell contents to another cell auto

Microsoft Excel 2002 (full product)
November 4, 2009 at 22:32:07
Specs: Windows XP
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

See More: Save cell contents to another cell auto

Report •

November 5, 2009 at 05:17:49

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
    'save the workbook
End With
Exit Sub

'error handler
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 '



Report •
Related Solutions

Ask Question