Getting Excel to Copy Data From One Cell to Another Depending on Date

If your workbook isn't guaranteed to be open at the end of each month I would update the value every time it gets opened, like(Should be placed in ThisWorkbook):

'Runs when you open the workbook Private Sub Workbook_Open() 'Loops through U3 to the last used cell in that column For Each c In Range(Cells(3, 21), Cells(Rows.Count, 21).End(xlUp)) 'Applies the J7 value to the current month and exits the sub If Month(c) = Month(Now) Then c.Offset(, 2).Value = [J7]: Exit Sub Next c End Sub 

Also, not that it matters but, I would apply the following formula in U3:U14 to always get the correct dates:

=EOMONTH(DATE(YEAR(TODAY()),ROW()-2,15),0) 

Okay, I'm still not super sure what the question is and I know more Access VBA than Excel VBA, but here's something that might help to find a solution.

You can make a check date function that returns a Boolean value:

Public Function EoMonthCheck() As Boolean Dim eo_month As Date, today As Date eo_month = Format(WorksheetFunction.EoMonth(Now(), 0), "yyyy-MM-dd") today = Format(Now(), "yyyy-MM-dd") If today = eo_month Then EoMonthCheck = True Else EoMonthCheck = False End If End Function 

And the,, to add a value to the "W" column, we might use something like this:

Public Function AppendValue(Optional target_cell As String = "J7") ''' This could be a subroutine, too, I guess, since we're not returning anything. Dim i As Integer ''' Activate whatever sheet you want to work with Worksheets("Sheet1").Activate If EoMonthCheck() = True Then ''' Look up the bottom of the 'W' column and find the first non-empty cell ''' Add 1 to that cell to get you to the next cell (the first empty one). i = Cells(Rows.Count, "W").End(xlUp).Row + 1 ''' Set the value of that empty cell in the 'W' column to the value of 'J7' ''' which will happen after we evaluate whether it is the end of the month. Cells(i, "W").Value = Range(target_cell).Value End If 

Then, you could maybe trigger that each time the workbook opens.