Vba to save range with formulas as values if date is less than today.

[FEMAR]

Hi,

I am a new member with no knowledge of Vba... I need help to save rows in a range containing formulas as values if the date in the row is less than today please. I have not been able to find any vba coding in my searches and apparently the above is not possible without the use of vba.

I am running 64-bit Windows 7 Ultimate with Excel 2010.



The Range in sheet "Monthly Totals" is "B4:L380"

Column "B" contains the dates in "1-Jan-19" format to compare with "D1'
"D1" is "=TODAY()" same format as above
"C4:L34" contains formulas that needs to be saved as Values if the date in "B4:B380" is less than "D1"
If possible I would like the macro to run when the file is opened.


To Summarize: If the date in "B12" is less than "D1", then Copy "C12:L12" and paste as values in "C12:L12" on the same sheet. Same for "B13" and so on.

I would not even know where to start...

Any assistance will be greatly appreciated.

[大灰狼1976]

Hi FEMAR, something like this:

Private Sub Workbook_Open()
Dim i&
With Sheets("Monthly Totals")
For i = 4 To 34
If .Cells(i, 2) < .[d1] Then .Cells(i, 3).Resize(, 10) = .Cells(i, 3).Resize(, 10)
Next i
End With
End Sub

[FEMAR]

Hi 1976!

Thanks for the response.

When I run the above code all the rows before my date in "D1" is blank - The formulas are removed but the values are not pasted.

[FEMAR]

Hi 1976,

I have made the below change and the code is working perfectly!

Thank You very much for your assistance. Much appreciated!!!

[FEMAR]

If .Cells(i, 2) < .[d1] Then .Cells(i, 3).Resize(, 10).Value = .Cells(i, 3).Resize(, 10).Value