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]

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("sheet3")
         .Range("O" & Target.Row).Value = .Range("O" & Target.Row).Value & vbLf & OldVal & " " & Target.Offset(, 1).Text
      End With
   End If
   Application.EnableEvents = True
End Sub

 

[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("sheet3")
         .Range("O" & Target.Row).Value = .Range("O" & Target.Row).Value & vbLf & OldVal & " " & Target.Offset(, 1).Text
      End With
   End If
   Application.EnableEvents = True
End Sub

It doesn't work at all Is it because it's called "sheet3"? The sheets names are "Mitch" and "Mitch Archive", I have also amended column for the comment box in "Mitch Archive" to "E" instead of "O"

 

[Fluff]

You will need to change the sheet name.

 

[Bellaanima7]

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("E" & Target.Row).Value & vbLf & OldVal & " " & Target.Offset(, 1).Text
End With
End If
Application.EnableEvents = True
End Sub

I have changed the above and it doesn't do anything.

 

[Fluff]

Are you changing the value in col E or O?

 

[Bellaanima7]

So the original Value is in Main sheet called "Mitch" column "O", when it archives to sheet "Mitch Archive" in now will be placed to column "E".

 

[Fluff]

In that case you need to change both ranges on the other sheet to E

         .Range("E" & Target.Row).Value = .Range("E" & Target.Row).Value & vbLf & OldVal & " " & Target.Offset(, 1).Text

 

[Bellaanima7]

In that case you need to change both ranges on the other sheet to E

         .Range("E" & Target.Row).Value = .Range("E" & Target.Row).Value & vbLf & OldVal & " " & Target.Offset(, 1).Text

Perfect <3 ! It works as expected. Thank you so much once again If I wanted to learn more VBA where is the best place to start?

 

[Fluff]

I learnt most of what I know from looking at the various questions on here & trying to find a solution.