VBA help needed - Move data from sheet1 to sheet2, delete previous data on sheet1 automatically

[Bellaanima7]

Hi All, I am trying to create a code that will automatically move the data after I start making changes from sheet "Mitch" and column "O" to another sheet that is named "Mitch Archive" in the same column "O". It would leave me with new data in sheet "Mitch" and previous data would be in "Mitch Archive". I also want it to add it to the same column and not override it. I do not want to delete previous archived data or add it in the row below. I want it all in the same cell ie. O2 - Can anyone help me please?

That's my code that is all messed up:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Comments As String
Sheets("Mitch").Select
Comments = Range("O2")
Sheets("Mitch Archive").Select
Sheets("Mitch Archive").Range("O2").Select
If Sheets("Mitch Archive").Range("O2").Offset(1, 0) <> "" Then
a = Sheets("Mitch Archive").Cells(Rows.Count, "O").End(xlUp).Row + 1
Sheets("Mitch Archive").Range("O" & a).Value = Sheets("Mitch").Range("O2").Value
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Comments
Sheets("Mitch").Select
Sheets("Mitch").Range("O2").ClearContent
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "O2" And Target.Value <> "" Then
      With Sheets("Mitch Archive")
         .Range("O2").Value = .Range("O2").Value & Target.Value
      End With
      Target.ClearContents
   End If
End Sub

 

[Bellaanima7]

Thank you for this, but what I meant is for new text to stay in "Mitch" "O2" section and the old one that was previously there to be moved and added to the "Mitch Archive" "O2".

After that I would like to have it duplicated for other "O3""O4" etc. Could you advise if it's possible to create it for all column, but to only move it by cell not by whole column?

Thank you

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim OldVal As Variant, NewVal As Variant

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 15 Then
      Application.EnableEvents = False
      NewVal = Target.Value
      Application.Undo
      OldVal = Target.Value
      Target.Value = NewVal
      With Sheets("Mitch Archive")
         .Range("O" & Target.Row).Value = .Range("O" & Target.Row).Value & OldVal
      End With
   End If
   Application.EnableEvents = True
End Sub

 

[Bellaanima7]

I amended the below, it works one time and then it says error438 and stops.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) = "O2" And Target.Value <> "" Then
With Sheets("Mitch Archive")
.Range("O2").Value = .Range("O2").Value & Target.Value
End With
Sheets("Mitch").Range ("O2")
End If
End Sub

How to make it work constantly?

 

[Bellaanima7]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim OldVal As Variant, NewVal As Variant

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 15 Then
      Application.EnableEvents = False
      NewVal = Target.Value
      Application.Undo
      OldVal = Target.Value
      Target.Value = NewVal
      With Sheets("Mitch Archive")
         .Range("O" & Target.Row).Value = .Range("O" & Target.Row).Value & OldVal
      End With
   End If
   Application.EnableEvents = True
End Sub

IT WORKS PERFECTLY AND I CANNOT THANK YOU ENOUGH <3 !! you are a master :~)

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Bellaanima7]

Good afternoon Fluff,

Could you kindly advise if you could make amendments to the code provided?

What I would like to add is some formatting. So once comment is moved to the archive to be added as a new bullet point so that the whole text in comment box is not so crowded and it’s more readable.

Thank you in advance ?

 

[Fluff]

How do you want it, a each value on a separate line?

 

[Bellaanima7]

How do you want it, a each value on a separate line?

Yes, Each new value that is moved to the archive comments box to be a new bullet point in new line, but in the same comment box ? is there any chance it could have also include the date that this comment was made on? I’ve got additional column P “Date of Last Comment” on the “Mitch” file that captures the date and time comment was made. Is it even possible to take this data and add it to the bullet point in archive tab, so that once last comment is archived it would be new bullet point with a date and time it was made on the “Mitch” tab or is it too complicated? ?