Add a new row when marked as yes but the date is updated

[DMO123]

Hi,

i am hoping someone can help. i have been trying to do the below but i am not getting close to what i want:

in columns B:J (starting on row 4) i have data. when "Yes" is marked in column H i need the sheet to copy the line add it to the bottom of the table with the date updated by however many months is written in column F for example:

Column B has dates so the date is 21-Mar-19 in column F i have 3 (meaning 3 months repetition) so when i mark column H as "Yes" the VBA will copy this row from B:J, add it to the bottom of the table updating the date in column B to 21-Jun-19 (from column F and the 3 month repetition).

can someone help?

thank you in advance.

 

[mrshl9898]

Hi,

Once the data is added to the bottom does this need to occur again?

 

[mrshl9898]

If no, then try:

Sub copybelow()


Dim rownum As Long
Dim Lastrow As Long
Dim counter As Long
Dim mynewdate As Date
Dim myolddate As Date
Dim sht As Worksheet


Set sht = ActiveSheet
Lastrow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
rownum = 4
counter = 1


Do Until rownum = Lastrow + 1
If Cells(rownum, 8) = "Yes" Then
Range(Cells(rownum, 2), Cells(rownum, 10)).Copy Cells(Lastrow + counter, 2)
myolddate = Cells(Lastrow + counter, 2).Value
monthadd = Cells(Lastrow + counter, 6).Value
mynewdate = DateAdd("m", monthadd, myolddate)
Cells(Lastrow + counter, 2) = mynewdate
counter = counter + 1
End If
rownum = rownum + 1
Loop


End Sub

 

[DMO123]

@mrshl9898 tank you - yes it does need to occur again -sorry!

 

[DMO123]

Also i tried the code toy gave and it works but i would need it to paste as a value (if possible) as the data is pulled from another table.

 

[DMO123]

So looking into this further i have made this work with a button - would it be possible to make it run in the background? if so how do i do this?

 

[mrshl9898]

Hi,

So how often does it need to reoccur? It could go on forever if there's no limit.

Updated below for values

Sub copybelow()




Dim rownum As Long
Dim Lastrow As Long
Dim counter As Long
Dim mynewdate As Date
Dim myolddate As Date
Dim sht As Worksheet




Set sht = ActiveSheet
Lastrow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
rownum = 4
counter = 1




Do Until rownum = Lastrow + 1
If Cells(rownum, 8) = "Yes" Then
Range(Cells(rownum, 2), Cells(rownum, 10)).Copy
Cells(Lastrow + counter, 2).PasteSpecial xlPasteValues
myolddate = Cells(Lastrow + counter, 2).Value
monthadd = Cells(Lastrow + counter, 6).Value
mynewdate = DateAdd("m", monthadd, myolddate)
Cells(Lastrow + counter, 2) = mynewdate
counter = counter + 1
End If
rownum = rownum + 1
Loop

What do you mean by in the background? What would you like it to do?

 

[DMO123]

thank you for this - by running in the background i mean when a value is entered in that column the VBA is triggered. so when i enter yes then click of the cell the VBA runs, it could be when i move sheets if easier?