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

123NC

New Member
Joined
Sep 10, 2019
Messages
3
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. :confused:
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
Task NameSeriesKick Off Date Activity FrequencyEnd DateActivity Frequency 2Kick Off DateDuplicate
A22021/03/24292021/04/22302021/02/223
B62025/08/29892025/11/26902025/05/313
A22021/04/23292021/05/22302021/03/24
B62025/11/27892026/02/24902025/08/29
A22021/05/23292021/06/21302021/04/23
B62026/02/25892026/05/25902025/11/27

<tbody>
</tbody>
 

123NC

New Member
Joined
Sep 10, 2019
Messages
3
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

New Member
Joined
Sep 10, 2019
Messages
3
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
 

Forum statistics

Threads
1,081,936
Messages
5,362,249
Members
400,672
Latest member
ExcelGrasshopper

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top