Tracking days and values

[khalil]

hi all

please help,

cell A1 always has a number changes once every day ,( only Cell A1)

i want to track that number with that date, i come back to it any time,

thanks

 

[jeffreybrown]

Need a little more info here.

cell A1 always has a number changes once every day (only Cell A1).

The value in A1, how does it get changed everyday?

i want to track that number with that date, i come back to it any time.

Does this mean you simple want to track the number and what date it is associated with, but no other datat? Do you want a list stored on another sheet with the number and date?

 

[khalil]

thanks for the reply,

it comes from another cell from another tap, paste special link

thanks

 

[khalil]

yes,
we can store the list in the same sheet

 

[jeffreybrown]

Okay then try this...

Right click sheet tab >> view code >> paste code on right side of screen

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim oldvalue As Long
    oldvalue = Range("A1")
    Dim LR As Long
    LR = Range("F" & Rows.Count).End(xlUp).Row + 1
    Set Rng = Target.Parent.Range("A1")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    Range("F" & LR) = oldvalue
    Range("G" & LR) = Date
End Sub

Everytime you paste something in A1, columns F/G will recieve the old number and a date.

 

[khalil]

i found that it is better to store the list in another tab,
what code shall i change and what tab shall i paste the code in

thanks for the help

 

[jeffreybrown]

Notice the addition of the with statement. Sheet name is coded as "List", please update to what you call the sheet. Values are pasted in columns A/B

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldvalue    As Long
    Dim Rng         As Range
    Dim LR          As Long
    
    oldvalue = Range("A1")
    Set Rng = Target.Parent.Range("A1")
    
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    
    With Sheets("List")        
        LR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & LR) = oldvalue
        .Range("B" & LR) = Date
    End With    
End Sub

 

[khalil]

cell A1 should be in the same sheet also

 

[jeffreybrown]

cell A1 should be in the same sheet also

I don't know what this means.

Let's set the ground rules.

In A1 of a sheet (let's call this the source sheet) you have a number.

Everyday you paste over this value with a value which was stored somewhere else.

When you paste over A1 in the source sheet the old value is now pasted onto sheet "List" in column A with a date in column B.

Please clearly list what is not right with this process?

 

[khalil]

sorry for the confusion here

stored columns A/B will be in sheet called "Cancellations"

and N8 cell where the value is also found in the same sheet,




i named A1 cell as an example.....
shall i wait for tomorrow to come in order to see the result in columns A/B

thanks