Unknown title Formatting or Function enquiry

[colinheslop1984]

I would like a selection of cells where data will be inputted say once per week and then ideally this info would be stored on a separate sheet, if this makes sense.

For example, if I have a range of cells A1:A10, where each Monday I will enter some numerical data. Is it possible that this data then gets stored in a table format so that I hold a record for the numbers I entered in these cells each week?

I know it would probably make more sense doing it the other way round which would also be simpler, but if I can do it this way it would be preferred.

 

[Yongle]

Perhaps something like this

Make sure your workbook contains 2 sheets - Sheet1 and Sheet2
Add code as instructed
Test by inserting some values in A:A10 in sheet 1 and right-click on cell A11 to trigger the macro

Add this code to sheet1 sheet module by right-clicking on sheet1 tab \ View Code \ paste code into that window \ {ALT}{F11} to go back to Excel

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim rng     As Range:   Set rng = Range("A1:A10")
    If Target.address(0, 0) = "A11" Then
        Cancel = True
        If MsgBox("Add to sheet2", vbYesNoCancel) = vbYes Then
            With Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
                .Value = Format(Now, "hh:mm:ss")
                rng.Copy .Offset(1)
                rng.ClearContents
               .Parent.Activate
            End With
        End If
    End If
End Sub