how to remove values once cells been updated

[Patriot2879]

hi I have the code below where I have added a vlookup, but once the cells are updated I want to remove the vlookup, I have tried '.value=.value' but that didn't work can you help please?

Private Sub CommandButton1_Click()
 Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Set copySheet = Worksheets("Jun")
  Set pasteSheet = Worksheets("Jun")
  
      Set r = Range("AP2", Range("AP" & Rows.Count).End(xlUp))
    Range("A2").Resize(r.Rows.Count).Value = r.Value
  
  Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
  On Error Resume Next
  Range("AJ2:AJ" & Lastrow).SpecialCells(xlBlanks).Value = Range("AJ2").Value
  On Error GoTo 0
Range("AJ2", Range("AJ2").End(xlDown)).Copy Range("I2")
Range("AB2", Range("AB2").End(xlDown)).Copy Range("E2")
Range("AD2", Range("AD2").End(xlDown)).Copy Range("F2")
Range("AO2", Range("AO2").End(xlDown)).Copy Range("G2")
Range("AG2", Range("AG2").End(xlDown)).Copy Range("H2")
Range("AS2", Range("AS2").End(xlDown)).Copy Range("M2")
Range("C2:C" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:D,3,FALSE)"
Range("B2:B" & Lastrow).Formula = "=IF(A2=A1,0,1)"
Range("D2:D" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:E,4,FALSE)"
Range("J2:J" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:K,10,FALSE)"
Range("K2:K" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:L,11,FALSE)"
Range("L2:L" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:M,12,FALSE)"
Range("N2:N" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:O,14,FALSE)"
End Sub

 

[Repush]

again: you should invest some time in reading how to operate the VBA-editor, how to inspect local variables and so on
I hope you have "View" in the menu (File, Edit, View, ...), choose local variable

 

[Patriot2879]

Hi, is there any other coding I could try for this query at all? hope you can advise.

 

[Repush]

just one thought: the code works on the active sheet
is the commandbutton on the same sheet?

 

[Patriot2879]

hi good morning, yes command button is on same sheet that I want to update, the data is collated from other sheets to go on the sheet I want to update where the button is.

 

[Patriot2879]

I hope you can help please