Change data based on date field

[mtbe]

I have a file, a portion is attached, where I want to paste the value of the formula (in this case, just that of the external data) once it has grabbed the data from an external source.

If the first time I open the file is Jan 15, I want it to grab the data in columns C, E and G from Jan 1 to Jan 14 and paste each cell's value in the same cell. Any data on Jan 15 or past Jan 15, I don't want the value (hasn't been collected yet).

If I open the file again on Jan 18, I need the data from Jan 15 to Jan 17.

I have this macro which works well for grabbing the data and pasting the value, but I am struggling doing this based on the date.

Dim WS As Worksheet
Dim Rng1 As Range
Dim Cell As Range
Dim lrow As Long

lrow = Cells(Rows.Count, 1).End(xlDown).Row

On Error Resume Next

For Each WS In ActiveWorkbook.Worksheets
    With WS
        On Error Resume Next
        Set Rng1 = Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
            If Not Rng1 Is Nothing Then
                For Each Cell In Rng1
                If Left(Cell.Formula, 3) = "=AT" Then 'replaces only the externally retrieved data with the value
                    Cell.Value = Cell.Value
                End If
                Next
            End If
    Set Rng1 = Nothing
End With

Next

Any suggestions on limiting this by date in the first column?

[DonkeyOte]

I have a file, a portion is attached...

No attachment. Are the dates stored in a header row of some sort ?

[mtbe]

Oops....attached now:

[DonkeyOte]

Given you have external links obviously this is hard to validate but I think you're saying you want to leave the formulae in place for future dates, correct ?

Based on your existing code, perhaps then:

For Each WS In ActiveWorkbook.Worksheets
    With WS
        On Error Resume Next
        Set Rng1 = .Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        If Not Rng1 Is Nothing Then
            For Each Cell In Rng1
                If Left(Cell.Formula, 3) = "=AT" And Cell.Offset(,1 - Cell.Column) <= Now Then
                    Cell.Value = Cell.Value
                End If
            Next
        End If
        Set Rng1 = Nothing
    End With
Next

the above assumes date value is always in 1st column of sheet - if this is not the case then obviously things get a little more complicated and we'd need more info.