Change log with command button

[Hussainalieng]

Hello All - new user here! I was hoping to get some help to create a macro to do the following;

- I have a spreadsheet that tracks financial data through time
- 'data' tab has data in random cells i.e. A1, A2, B1 and B2
- data in 'data' tab is updated periodically, say once every few days
- after pressing a command button, data in cells A1, A2, B1 and B2 is copied and pasted onto a 'change_log' tab
- 'change_log' tab will time stamp data, and add new values under previous data
- 'change_log' tab will add data in specified cells, i.e. D2, D3, D4 and D5
- data in 'data' tab is then updated with new value
- process repeats, with historical financial data saved in the 'change_log' tab

I'm not familiar with VBA or macro code, so was hoping to get some help with examples to achieve what I'm after.

I hope this makes sense. I've attached a simple spreadsheet to try and ilustrate.

Thanks in advance for your help.

[BadlySpelledBuoy]

Hi and welcome.

Could you show us what your expected output would look like after a couple of presses of the command button?

Should it clear the previous data from sheet1?
Does the amount of data on sheet1 vary?

BSB

[Sintek]

If I understand correctly...One way...

Private Sub CommandButton1_Click()
Dim cnt As Long, nr As Long
With Sheets("Data").Cells(1).CurrentRegion
    cnt = .Rows.Count - 1: .Columns(1).Offset(1).Resize(cnt, 2).Copy
End With
With Sheets("Change_Log")
    nr = .Cells(.Rows.Count, 4).End(xlUp).Row + 1
    .Range("D" & nr).PasteSpecial xlPasteValues
    .Range("A" & nr).Resize(cnt, 3) = Array("Time Stamp", Date, Format(Now(), "HH:MM:SS"))
End With
Application.CutCopyMode = False
End Sub

After a 2 presses...?
Untitled.png

[Hussainalieng]

Thanks so much BSB & Sintek. Really appreciate the assistance.

- after pressing the command button, data should not be cleared from the 'data' tab
- the amount of data on 'data' tab does vary, and can be located anywhere on the sheet.
- output on the 'change_log' tab should look something like shown below

I should have clarified that the data on the 'data' table can be located anywhere on the sheet, not just as an array. The 'change_log' should display the values adjacent to the 'label' cell horizontally for each timestamp row. This is to allow charting of a change log to see how the data varies though time.

An updated example of the 'data' tab and 'change_log' tab is shown below. Thanks again so much for your help!

'Data' tab format:

Data 1
Label 1 123
Label 2 456


Data 2
Label 3 789
Label 4 987

Data 3
Label 5 764


'Change_log format'

Label 1 Label 2 Label 3 Label 4 Label 5
Timestamp 6/02/2022 10:38 AM 123 456 789 987 764
Timestamp 7/02/2022 8:08:29 AM 1234 4567 7890 9876 7643

[Sintek]

Small data set...no need for arrays...Here is a easy enough loop to understand...
However, something tells me that there is more to this than meets the eye so to say...
i.e. look at data5 in attached file...I added this after the first run of the macro...If Label6 does not yet exist the data will be entered into a new column and values entered just below...Does this not have to be entered into first new row based on column A?
Anyway have a look and explain requirement in more detail...

Private Sub CommandButton1_Click()
Dim Fnd As Range, cell As Range, lc As Long, lr As Long, nr As Long
With Sheets("Change_Log")
    For Each cell In Sheets("Data").UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(cell) Then
            lc = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
            If lc = 2 Then
                lc = 5
            Else
                Set Fnd = .Rows(1).Find(cell.Offset(, -1).Value, , xlValues, xlWhole)
                If Not Fnd Is Nothing Then lc = Fnd.Column
            End If
            .Cells(1, lc) = cell.Offset(, -1)
            nr = .Cells(.Rows.Count, lc).End(xlUp).Row + 1
            .Cells(nr, lc) = cell: If .Cells(nr, 1) = "" Then .Cells(nr, 1).Resize(, 3) = Array("Time Stamp", Date, Format(Now(), "HH:MM:SS"))
        End If
    Next cell
    .Activate
End With
End Sub

[Hussainalieng]

Thank you Sintek! Your observation is correct ... I'm trying to work out exactly how to describe what I'm trying to achieve :-)

Your code was super helpful and worked as intended, however when I added it to the actual dataset, it ended up logging almost every cell in the 'data' sheet.

To overcome this, is there a way for the user to specify exactly which cells in the 'data' tab are copied and tracked in the 'change_log'? I've used the 'Name Manager' feature as the way to specify which cells are to be copied, but any easier / less complicated solution is welcome (i.e. using a list with cell references etc...)

That is:
- in the 'change_log' tab, the first row will specify the labels (as text only) corresponding to the data in the 'Name Manager' which should be copied. I.e. 'Label_1', 'Label_2', 'Label_3' and so on
- in the 'data' tab, the cells to be copied have a defined 'Name', which corresponds exactly to the first row of the 'change_log' tab
- i.e. the 'Name' of cells B2, B3, D10, D11 and G16 in the 'data' tab and 'Name Manager' is 'Label_1', 'Label_2', 'Label_3', 'Label_4' and 'Label_5' respectively
- when the command is run; - the script will read the first row of the 'change_log' tab, - match to the corresponding cell in the 'data' tab via the 'Name Manager'- pull the data from the 'data' tab and log it with a timestamp in the 'change_log' tab

In the scenario a new label is created in the 'data' tab and 'Name Manager' after many changes have been logged, it is OK if the data is entered into a new column on the 'change_log' and the first value is entered just below. I will simply manually paste it down to the row with the correct time stamp, and all previous timestamps in that new column will be manually filled with zeros.

Going back to the original scope of question: I'm looking for a way to copy specified data, which can be located anywhere in the 'data' tab, upon pressing a command button, and paste it with a timestamp on the 'change_log' tab. The data will then be manually updated (this is financial data which changes every few days), and upon pressing the command button again, historical changes can be monitored via the 'change_log'.

Thanks again for your advice, I really appreciate it.

[Sintek]

Your code was super helpful and worked as intended, however when I added it to the actual dataset, it ended up logging almost every cell in the 'data' sheet.

I see this happening 99% of the time...That is why it is important uploading a sample file representing actual file...

Going back to the original scope of question: I'm looking for a way to copy specified data, which can be located anywhere in the 'data' tab

You are contradicting yourself...This was the initial request, then you changed it to only named ranges based on specific cells...So what is it...
I suggest uploading a sample file depicting your actual file and explain in detail what you want to happen each time the button is pressed...
Explain your manual inputs before each button press and show your actual result required...Can go around in circles offering code based on assumptions...