Time stamp VBA - Certain cells to stop updating with Refresh All

[Buck2919]

Hi,

I've created the below VBA to insert a timestamp once a value is added into a certain cell.

Function Timestamp(Reference As Range)
If Reference.Value <> "" Then
Timestamp = Format(Now, "dd-mm-yyyy hh:mm:ss")
Else
Timestamp = ""
End If
End Function

However i would like certain cells within one sheet not to update when i press "Refresh All", to update a series of pivot tables.

Thank you in advance

 

[Misca]

I'm a bit confused. Looks to me you're basically asking nothing but describing three separate things in your post.

There's your TimeStamp function that returns a string and I believe it works just right.

Then there's the certain cells that update - containing formulas, I believe - that you'd like to remain static. If they're always the same cells or the cells can be set from values in other ranges you might need a macro to do that. If they contain formulas, that is.

And then there's the Refresh All -thing that updates the pivot caches and Power Query tables and stuff like that. If the ranges you don't want to update on Refresh All happen to be Power Query tables, all you need to do is uncheck the "Refresh this connection on Refresh All" option from the Query Properties.

I am sure there's some kind of a connection between these things but I can't see it.

 

[Buck2919]

Ho Misca,

Thank you for your response.

Maybe I have explained this poorly.

I have the timestamp as mentioned previous which is created when a value becomes 0 and the status shown below in column C2 becomes “Complete” i.e. in Cell D2 is the formual =Timestamp(C2)

However, in the same file I have a number of standard pivot tables which I need to refresh daily, where I use the “Refresh All” function to update them.

When I “Refresh All” my Time Stamp updates and changes the date to the “Current” time.

I only want my timestamp to update when the Status changes in Cell C2.

Hope that makes more sense.

 

[Misca]

Don't put the TimeStamp in the cell as a formula but have a macro "stamp" it on cell change event on column C:

Paste the following code to your worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub 'Does nothing if several cells are changed

If Not Intersect(Target, Range("C:C")) Is Nothing Then  'Only if cell changed in column C:
    Target.Offset(, 1).Value = Timestamp(Target)    'Put the TimeStamp on column D
End If

End Sub

If you want to make sure the TimeStamp stays in the cell even if you happened to delete the existing value in column C you might want to use:

 

[Misca]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub 'Does nothing if several cells are changed

If Not Intersect(Target, Range("C:C")) Is Nothing And Target.Offset(, 1).Value = "" Then 'Only if cell changed in column C and D is empty:
    Target.Offset(, 1).Value = Timestamp(Target)    'Put the TimeStamp on column D
End If

End Sub