How to create a VBA to move one data point to another column to create historical data table.

[beccadavis3]

I have one data point in excel (K28) that I want to autmoatically move everytime the spreadsheet is opened to another column (N7) to start a table creating a historical trend. Therefore the data point that the information is being moved to will need to automatically populate on the next row. Is there a way to create a Macor or VBA to do this?

 

[SunnyAlv]

like this ?

*Paste VBA Code in 'ThisWorkbook' object, see below image

Private Sub Workbook_Open()
    Dim ws As Worksheet, ls As Long
    
    Set ws = ThisWorkbook.Sheets(1)  'Change with ur sheet name
    ls = ws.Cells(Rows.Count, "N").End(xlUp).Row + 1
    
    With ws
        .Range("N" & ls).Value = .Range("K28").Value
        .Range("K28").ClearContents 'if u want to delete K28 after workbook opened
    End With
    MsgBox "Data Moved !"
End Sub

 

[kvsrinivasamurthy]

Try this worksheet event.

Private Sub Worksheet_Activate()
Dim ro&
If Range("K28") <> "" Then
If Range("N7") = "" Then ro = 7 Else ro = Range("N" & Rows.Count).End(xlUp).Row + 1
Range("K28").Cut Range("N" & ro)
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Select worksheet in drop down instead of General
Paste the code
Close the VB window.
Save the file as .xlsm

 

[beccadavis3]

like this ?

*Paste VBA Code in 'ThisWorkbook' object, see below image

Private Sub Workbook_Open()
    Dim ws As Worksheet, ls As Long
   
    Set ws = ThisWorkbook.Sheets(1)  'Change with ur sheet name
    ls = ws.Cells(Rows.Count, "N").End(xlUp).Row + 1
   
    With ws
        .Range("N" & ls).Value = .Range("K28").Value
        .Range("K28").ClearContents 'if u want to delete K28 after workbook opened
    End With
    MsgBox "Data Moved !"
End Sub

View attachment 120391

This worked great, thank you! Now is there a way to add a date and time stamp next to the moved line item?