VBA script req'd to copy range from a column headed '=TODAY()-1' in Excel

[PerseveringHarold]

I have a dataset that updates daily. I need to compare each day's new data range with the equivalent range from the day before.

So my Excel sheet has a column headed '=TODAY()' for the new data, and a column headed '=TODAY()-1' for the previous day's data.

I need my VBA script to find the column headed '=TODAY()-1' and copy the range below it into a column in a separate sheet.

I've hunted around for similar threads, but generally they tend to refer to a fixed date, rather than a dynamic date header like '=TODAY()-1'.

Does what I'm looking for seem possible? Thanks.

 

[LazyBug]

Run from the sheet where the table and formulas are.

Sub FindMyFormula()
Application.ScreenUpdating = 0
Dim m As Range
    With ActiveSheet
        Set m = .UsedRange.Cells.Find("=TODAY()-1", , xlFormulas, xlWhole)
        Range(m(2, 1), m(2, 1).End(xlDown)).Copy
    End With
    Sheets("Sheet2").Range("A5").PasteSpecial xlPasteValues
With Application
    .CutCopyMode = 0
    .ScreenUpdating = 1
End With
End Sub

 

[PerseveringHarold]

Run from the sheet where the table and formulas are.

Sub FindMyFormula()
Application.ScreenUpdating = 0
Dim m As Range
    With ActiveSheet
        Set m = .UsedRange.Cells.Find("=TODAY()-1", , xlFormulas, xlWhole)
        Range(m(2, 1), m(2, 1).End(xlDown)).Copy
    End With
    Sheets("Sheet2").Range("A5").PasteSpecial xlPasteValues
With Application
    .CutCopyMode = 0
    .ScreenUpdating = 1
End With
End Sub

Thank you so much for this reply. I checked back a few times after posting my OP, but seem to have missed any notification that you replied afterwards. Top work, thank you.

 

[LazyBug]

Glad I could help. Thank you for the feedback.