Copy and PasteSpecialValues of within a User-Defined Function

[BigShanny]

I have a user defined function that calculates the current date if the value of another cell (FileName) is not "". In other words, when someone updates a new file name, the date of when they did the update auto-populates. Once this value is calculated, I would like to copy and paste it's value only (PasteSpecial Values) back into the same cell. Essentially, once the value is calculated, I want to kill the formula so that it cannot be used again. I just want the initial calculated value to remain. I've tried calling a sub-routine (Hardcode) with no luck. I'm honestly not sure if this is the best approach. Even if it is, I can't seem to get the syntax correct. Any thoughts on the most efficient way to accomplish my desired task?

Function LoadDate(FileName As String)

    
    If (FileName <> "") Then
    
        LoadDate = Date
        Call Hardcode (LoadDate)
                
    Else
        
        LoadDate = ""
            
    End If
    
            
      
End Function

Sub HardCode(LoadDate As Date)

Range(LoadDate).Copy
Range(LoadDate).PasteSpecial xlPasteValuesAndNumberFormats


End Sub

 

[Joe4]

Welcome to the Board!

Among other issues, one of your biggest issues is this here in your Sub Procedure:

Range(LoadDate).

If "LoadDate" is a date, it does not make any sense to put it in a range reference.
That range reference would need to be a cell address or a named range.

I think that some automated Event Procedure code would serve you better that trying to do what you are doing.
What you are trying to do is called "Date Stamping", and there are literally hundreds, if not thousands of posts on this board about it (it gets asked all the time).
Essentially, what Date Stamping is is when a certain range is updated with a value, another cell in the same row is populated with the current date.

So you have two options here:
1. You can search this Board on "Date Stamping", and get the VBA code needed from those old posts.
2. You can provide us with the exact details of how you want this to work, i.e. what exact range are you watching for updates? What column in that same row should it put the current date in?

 

[BigShanny]

Hi Joe,

Thanks for your reply. I should explain, in more detail, what my problem is. The date-stamping works correctly when I use the UDF on its own without the Sub routine. Like this:

Function LoadDate(FileName As String)

    
    If (FileName <> "") Then
    
        LoadDate = Date
                
    Else
        
        LoadDate = ""
            
    End If
             
End Function

I found one of your replies that uses "Date" to time stamp the field but I want it to remain static and it's not. The date stamp keeps refreshing as other cells are updated. It's as if any change made in the spreadsheet somehow updates the date again. My real goal is to make the date calculated by "LoadDate" actually stay the same and not change as the user updates other cells in the spreadsheet. This is why I am trying to paste values.

Here's what the spreadsheet looks like:

SSN

Client

Retiree Name

FileName

Type

Load Date

Hard Code

File Age

Attempt 1 User

Attempt 1

Attempt 1 Date

Attempt 2 User

Attempt 2

Attempt 2 Date

Attempt 3 User

Attempt 3

Attempt 3 Date

Completed

Completed Date

999999999

American Publishing

Jerry Allen

Age-In_TestFile.xlsx

Medicare

1/22/2025

1

Shannon

Outbound Call, Left Voice Mail

1/23/2025

Shannon

Outbound Call, Left Voice Mail

1/23/2025

No

Thanks in advance for any help.

 

[Joe4]

That is exactly what date stamping does (but you would NEVER use a UDF, as it enters a hard-coded date, never a formula)! It may just need to be tweaked to suit your exact situation.
Please provide us with the exact details, and we can help you set it up. But we need to know the exact ranges involved!

What columns is it watching for an entry?
And then what column would the date go into?

 

[BigShanny]

That is exactly what date stamping does (but you would NEVER use a UDF, as it enters a hard-coded date, never a formula)! It may just need to be tweaked to suit your exact situation.
Please provide us with the exact details, and we can help you set it up. But we need to know the exact ranges involved!

What columns is it watching for an entry?
And then what column would the date go into?

Hello again,

When the column "FileName" (or Column D) is populated, I want to hard code the date of when that happened in "Load Date" (or Column F). I want column F to remain blank or "" until File Name is populated.

The spreadsheet again for reference:

SSN	Client	Retiree Name	FileName	Type	Load Date
999999999	American Publishing	Jerry Allen	Age-In_TestFile.xlsx	Medicare	1/23/2025

Thanks!

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code into the VB Editor window that pops-up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if any updates made to column D
    Set rng = Intersect(Target, Columns("D:D"))
    
'   Exit if no updates to column D
    If rng Is Nothing Then Exit Sub
    
'   Loop through cells just updated in column D
    For Each cell In rng
'       See if changes after row 1 and column F is blank
        If (cell.Row > 1) And (cell.Offset(0, 2) = "") Then
'           Put date stamp in column F
            Application.EnableEvents = False
            cell.Offset(0, 2) = Date
            Application.EnableEvents = True
        End If
    Next cell
            
End Sub

This will run automatically. If you update column D, and column F of that same row is blank, it will hard-code the current date in column F.

 

[BigShanny]

Hi Joe,

I copied and pasted into the VBA window that pops up for the sheet in which I want the dates to hard code. After, It didn't do anything to column F when I updated column D. I follow along with the logic in the code and it seems like it would work (with my limited understand), but nothing happens. Am I missing something?

Thanks

 

[Joe4]

Are you absolutely sure column F is blank to start?
If you have those old formulas in there, please remove them all.
Empty column F values should be totally blank.

 

[BigShanny]

Hi Joe, It's working now. Column F was blank. I have another Sub within that sheet so I pasted the new code below it in a separate Sub. It may have been a case of a stray character somewhere that I missed. I deleted everything in the VBA and re-pasted both subs. Working fine now.

I really appreciate your help. Exactly what I needed.

I used column F as an example to keep it simple for my post. However, there are a few other fields within the sheet that I need to date stamp.

I will try to get it working by modifying your code with additional loops. Hopefully I can figure it out without having to post again. Example if you are free to point me in the right direction or give further guidance:

Attempt 1 Date (Column J) should Date Stamp if Attempt 1 User (Column H) User AND Attempt 1 (Column I) are not empty.

Attempt 1 User	Attempt 1	Attempt 1 Date
Shannon	Outbound Call, Left Voice Mail	1/23/2025

 

[Joe4]

That gets a little trickier, and I would probably change the methodology we used.
The following code works, as long as you are only updating one cell at a time for columns D, H, and I (i.e. you are use Copy/Paste to paste to multiple cells at once in those ranges).
I added lots of documentation so you can hopefully see how it works:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long
    Dim c As Long
    
'   Exit sub if multiple cells are updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Get row and column of update
    r = Target.Row
    c = Target.Column
    
'   Exit if header row updated
    If r = 1 Then Exit Sub
    
    Application.EnableEvents = False
    
'   See which column was just updated
    Select Case c
'       If column D (4th column)
        Case 4
'           Check to see if column F is empty
            If Cells(r, "F") = "" Then
'               Put date stamp in column F
                Cells(r, "F").Value = Date
            End If
'       If column H (8th column) or I (9th column) updated
        Case 8, 9
'           Check to see if both columns H and I are populated and J is empty
            If (Cells(r, "H") <> "") And (Cells(r, "I") <> "") And (Cells(r, "J") = "") Then
                Cells(r, "J").Value = Date
            End If
    End Select
    
    Application.EnableEvents = True

End Sub

If you have others to add, you would just add new "Case" clauses, for whichever columns you are watching for updates.