Command button to move cell value to another cell, without cell error =#REF-CodePudding

Hello Everyone!

I'm working on an excel 2007 project for my work. I'm trying to build a command button to move one cell to another cell.

Private Sub CommandButton1_Click()
  ActiveSheet.Unprotect Password:="123"
  Select Case MsgBox(Buttons:=vbYesNo   vbQuestion, Prompt:="Are you sure? ")
    Case vbYes
      Range("A2").Cut Range("B2")
    Case vbNo
  End Select
  ActiveSheet.Protect Password:="123"
  ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

This is my current VBA code for cut & paste from one cell to another cell.

But now I'm having another issue every time I press the command button. Please help me to solve this issue.

screenshot

This is the default view of my project. In the cell, A2 has a SUM of range A4:A8 & the cell D2 is =A2, E2 is =B2

The Problem is after clicking on the button:

screenshot

As you can see, D2 is supposed to be =A2 now changed to =B2 & E2 supposed to be =B2 but now =#REF! error.

I could not find any solution for this issue, So please help me to solve this issue.

CodePudding user response：

If you want to keep the SUM formula in B2 and not just the value, you could use this instead:

Range("A2").Copy Range("B2")
Range("A2").ClearContents

Also update the formula to use $

=SUM($A$4:$A$8)