Can a Command Button's position be kept moving along the TargetCell ROW in the same Column

Show us your attempts for the other questions.

Dear Dave,

I have made a few changes in the code you provided as well as added some new code for the command buttons, please advise if thats correct though its working..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column <> 2 Or Target.Row = 1 Then Exit Sub
    If WorksheetFunction.CountA(Target.Offset(0, -1).Range("A1:L1")) < 10 Then Exit Sub
    
    With UserForm1
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With

End Sub

I also wanted to know about a strange thing that when I tried using the column names as it is it gave an incorrect answer..
I mean I had to use one column behind to refer the actual column.. in the code for the first button..

Now there's a slight problem as the copied values are getting copied with formulas and that creates a circulr reference so how do I amend that as the Maturity amount of the original active row becomes the Amount in the Renewed or COntinued FD..

Can you please make the existing formulas in the columns A, col K and Col L as values after using the formula as I want these formulas to also appear when the REnewed FD and Continue FD is in progress..lets say I have dragged the formulas till the row 11 that is for 10 records and then I add a FD by Renew FD then I need the formula in the column A to show the Sr. No as well as the col K to show the Maturity Amount and the col L the interest..

I dont know how to write these formulas in this button click event..


Please find my codes:

Private Sub CommandButton1_Click()
    Dim Rws As Long
    Rws = Cells(Rows.Count, "B").End(xlUp).Row + 1
    
    ActiveCell.Range("A1:B1").Copy Destination:=Cells(Rws, 2)
    ActiveCell.Range("J1").Copy Destination:=Cells(Rws, 4)
    ActiveCell.Range("D1:F1").Copy Destination:=Cells(Rws, 5)
    ActiveCell.Range("H1").Copy Destination:=Cells(Rws, 8)
    Cells(Rws, 9).Value = Application.WorksheetFunction.Text(Cells(Rws, 8).Value + DateDiff("d", ActiveCell.Range("G1"), ActiveCell.Range("H1")), "dd-mmmm-yy")
    UserForm1.Hide

End Sub

Private Sub CommandButton2_Click()
    Dim Rws As Long
    Rws = Cells(Rows.Count, "B").End(xlUp).Row + 1
    
    ActiveCell.Range("A1:B1").Copy Destination:=Cells(Rws, 2)
    ActiveCell.Range("J1").Copy Destination:=Cells(Rws, 4)
    ActiveCell.Range("D1:F1").Copy Destination:=Cells(Rws, 5)
    ActiveCell.Range("H1").Copy Destination:=Cells(Rws, 8)
    UserForm1.Hide
End Sub

Private Sub CommandButton3_Click()

Dim lastrow As Long
lastrow = ActiveSheet.UsedRange.Rows.Count

    Range("A" & lastrow, "A" & lastrow).EntireRow.Delete
    
End Sub

Private Sub CommandButton4_Click()
Dim activerow As Long
Dim lastrow As Long

activerow = ActiveCell.Row
lastrow = ActiveSheet.UsedRange.Rows.Count

    Range("B" & activerow, "B" & lastrow).EntireRow.Delete
    
End Sub