Paste Special Values based on the date

[timmcl_7]

Hi,

I have a workbook which contains lots of formula and is therefore very large (about 70 mb). The sheet pulls information from field instruments every hour of every day for the entire year.

I want to write a macro, that loops through a range (B5:B8764) and copies the entire row if the value in column B is less than =NOW() - 7 days. The copied rows should then be pasted with values only exactly where they were located e.g. row 5 should be pasted back to row 5 the only difference being only the values will be pasted.

Any help would be much appreciated.

Regards

 

[Sahak]

Try this:

Sub Macro1()
    Dim Dt As Range
    For Each Dt In Range("B5:B8764")
        If Dt.Value = Int(Now()) - 7 Then
            Dt.EntireRow.Copy
            Dt.EntireRow.PasteSpecial (xlPasteValues)
        End If
    Next
End Sub

 

[Biz]

Try code below

Sub Macro1()
    Dim Dt As Range
    For Each Dt In Range("B5:B8764")
        If DateValue(CDate(Dt.Value)) = DateValue(Int(Date) - 7) Then
            Dt.Value = Dt.Value
        End If
    Next
End Sub

Does it help?

Biz