help with dynamic macro

Hi all

Could someone please help.

Looking to change the value in the VBA to that of a said cell (A1 for the purpose of this example)

Basically I have a report generate at look 3 months in advance, I would ideally like to change that 3 month value from a cell and update the macro detail


(<= Date + 91) within the macro below is what I am trying to allow to be changed i.e. (<= Date + 182) to make the report look further in advance. Ideally trying make it something like (<= Date + A1 report worksheet) in reference to the cell which will dictate the length

Current VBA for static date used below

Dim wksSource As Worksheet
Dim wksDest As Worksheet
Dim Rng As Range
Dim Cell As Range
Dim NextRow As Long

'//Change the sheet name accordingly
Set wksSource = Worksheets("trainee all")

'//Change the sheet name accordingly
Set wksDest = Worksheets("Reports")

Set Rng = wksSource.Range("i3:u231")

For Each Cell In Rng
If Cell.Value >= Date - 900 And Cell.Value <= Date + 236 And Cell.Interior.ColorIndex = -4142 Then
NextRow = wksDest.Cells(wksDest.Rows.Count, "c").End(xlUp).Row + 1
wksSource.Cells(Cell.Row, "a").Resize(, 5).Copy wksDest.Cells(NextRow, "a")
Cell.Copy
wksDest.Cells(NextRow, "f").PasteSpecial xlPasteValues
End

Hopefully this makes sense,

Thanks in advance

apologies, noob error posting code, as to posting twice, not sure why that happened I never meant to post twice.

apologies again.