populate date in first cell based on previous cell plus 30 days

[leap out]

Hello
I want search for the containing in last cell in column B and copy & put in next cell in column A and add 30 days for cells in last cell in column A when show date in adjacent cell in column B without red color , copy to next cells just based on one condition when the date in last cell in column B is matched with date(today) in PC , then should copy to next cells in columns A,B
I want to do that when open workbook automatically .
original

DATE.xlsm
	A	B	C	D
1	FROM	TO	DE	VL
2	28/07/2023	27/08/2023
3	27/08/2023	26/09/2023
sh

should be

DATE.xlsm
	A	B	C	D
1	FROM	TO	DE	VL
2	28/07/2023	27/08/2023
3	27/08/2023	26/09/2023
4	26/09/2023	26/10/2023
sh

thanks

 

[duggie33]

Is this what you are looking for?

Book1
	A	B
1	FROM	TO
2	28/7/2023	27/8/2023
3	27/8/2023	26/9/2023
4	26/9/2023	26/10/2023
5	26/10/2023	25/11/2023
6	25/11/2023	25/12/2023
7	25/12/2023	24/1/2024
8	24/1/2024	23/2/2024
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=A2+30
A3:A8	A3	=B2

 

[leap out]

the matter is not easy as you did it .
surely not as you did it.
you misreading this

I want to do that when open workbook automatically .

should be by vba

 

[duggie33]

Let's see what you have got, and what is not working with what you have tried. Your description is lacking some details.

 

[awoohaw]

What do you want if you open and close the workbook 10 times in one day? Do you want those calculations happening EVERY time, or just each new month, or each new day?

 

[leap out]

Your description is lacking some details

so ask me to answer you .

 

[awoohaw]

so ask me to answer you .

I think @duggie33 is asking you to show what vba you have already created?

 

[leap out]

What do you want if you open and close the workbook 10 times in one day?

if it's matched with date today and open and close every time should not calculate every time , just one time.
just calculation if the date last cell in column B is matched with date in pc then should copy the last cell in column B to next cell in column A and add 30 days to next cell in column A to show in adjacent cell in column B .
the calculation doesn't depends new day or month as you said .
the standard is based on date today in last cell in column B with date today in PC .

 

[leap out]

I think @duggie33 is asking you to show what vba you have already created?

Sub ADD_DATE()
Dim lr As Long
lr = Range("b" & Rows.Count).End(xlUp).Row
If Range("b" & lr) = Date Then
Range("a" & lr + 1) = Range("b" & lr)
Range("b" & lr + 1) = Range("a" & lr) + 30
End If
End Sub

result when I run every time

DATE
	A	B	C	D
1	FROM	TO	DE	VL
2	28/07/2023	27/08/2023
3	27/08/2023	26/09/2023
4	26/09/2023	26/09/2023
5	26/09/2023	26/10/2023
sh

the row4 shouldn't show

 

[duggie33]

Try this...you may have to remove the "Sheet4".

Sub ADD_DATE()

    Dim lr As Long
    lr = Sheet4.Range("b" & Rows.Count).End(xlUp).Row

    If Sheet4.Range("b" & lr) = Date Then
        Sheet4.Range("a" & lr + 1) = Sheet4.Range("b" & lr)
        Sheet4.Range("b" & lr + 1) = DateAdd("d", 30, Sheet4.Range("a" & lr + 1))
    End If

End Sub