Exce VBA Code for Copy and Pasting Mutiple Rows and then modifing values in Cells keeping formual in place

[123NC]

Hi
I have a spreadsheet which contains mutiple different rows

I want to duplicate all the rows multiple times using a value in the duplicate colum
The Kick Off Date contains the formula =DATE(YEAR(G2),MONTH(G2),DAY(G2)+F2) and End Date contain the formula =DATE(YEAR(C2),MONTH(C2),DAY(C2)+D2)
When the 1st duplicate row is created i would like to copy the data from the Kick Off Date and paste values into Old Kick off Date
Then when the 2nd duplicate row its a copy of the 1st duplicate row and then copy the data from the Kick Off Date and paste the values into Old kick off date. ​

Any help would be grateful, i have been going round and round for several days trying all sorts of different solutions.
Thankyou

This is an example of my data. The first two rows are the original data and the other 4 rows are the duplicate data ​

[TABLE="width: 590"]
<tbody>[TR]
[TD]Task Name[/TD]
[TD]Series[/TD]
[TD]Kick Off Date[/TD]
[TD] Activity Frequency[/TD]
[TD]End Date[/TD]
[TD]Activity Frequency 2[/TD]
[TD]Kick Off Date[/TD]
[TD]Duplicate[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2021/03/24[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]2021/04/22[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2021/02/22[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2025/08/29[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]2025/11/26[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]2025/05/31[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2021/04/23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]2021/05/22[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2021/03/24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2025/11/27[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]2026/02/24[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]2025/08/29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2021/05/23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]2021/06/21[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2021/04/23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2026/02/25[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]2026/05/25[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]2025/11/27[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[123NC]

This is the code i have so far the duplication is working fine and i its copy the first row data to the second but not the second to third and so on.
Sub Macro9()
'
' Macro9 Macro
'
'
'
Dim FirstRow As Long
Dim LastRow As Long
Dim VInSertNum As Variant
Dim i As Long
Dim xRow As Long
'Number of rows to create
FirstRow = 1
LastRow = 10
xRow = 1
i = 1
Application.ScreenUpdating = False
Do While (Cells(xRow, "A") <> "")
VInSertNum = Cells(xRow, "H")
If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
Range(Cells(xRow, "A"), Cells(xRow, "H")).Copy
Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "H")).Select
Selection.Insert Shift:=xlDown


Range("C" & i).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[4]),MONTH(RC[4]),DAY(RC[4])+RC[3])"
Range("C" & i + 1).Select
Selection.Copy
Range("G" & i + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
i = i + 1

xRow = xRow + VInSertNum - 1
End If
xRow = xRow + 1
Loop
Application.ScreenUpdating = False
End Sub

 

[123NC]

Nailed it!!!! took me all day
Sub DupRows_UpdateDates()
'
'
'
'
Dim VInSertNum As Long
Dim xRow As Long
Dim count As Long
'Number of rows to create
xRow = 2

Application.ScreenUpdating = False
Do While (Cells(xRow, "A") <> "")
VInSertNum = Cells(xRow, "Y")
If (VInSertNum > 1) Then
Range(Cells(xRow, "A"), Cells(xRow, "Y")).Copy
Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "Y")).Select
Selection.Insert Shift:=xlDown

For count = xRow To xRow + VInSertNum - 2
Range("G" & count + 1).Value = Range("C" & count).Value
Next count

xRow = xRow + VInSertNum - 1
End If
xRow = xRow + 1
Loop
Application.ScreenUpdating = False
End Sub