Copy data and insert value from list

[RickWilliams]

Hi Guys,

Apologies for the simplistic nature of this request.

I have a list of rows that I need to duplicate 96 times adding in a value from a defined list into columns F2 and G2:

The list has 95 values:

The result would be having 95 copies of the rows ready for bulk loading:

This can be done by hand of course but thought best to see if there was a scripted approach as this will be a task that I will need to repeat going forward.
Thank you SO much in advance.

Rick

[#2 · Accepted solution]

@RickWilliams 

Maybe with these lines of code. An adjustment to the name of the worksheet containing the 95 values is required. You can click the button in cell I2 to execute the macro.

Sub multiply()

Dim i As Integer
Dim j As Integer
Dim z As Integer

z = 0
For j = 2 To 96
For i = 2 To 96

Cells(i + z, 6).Value = Cells(j, 3).Value
Cells(i + z, 7).Value = Cells(j, 4).Value

Next i
z = z + 95

Next j

End Sub

[#3]

@OliverScheurich 

This is FANTASTIC!  

Thank you so very much!

Just in the process of reverse engineering your script so I can fully appreciate/understand!

Thank you again

Rick

[#4]

@OliverScheurich 

Apologies for further queries, as a separate script, how would I go about copying a block of rows/columns and replicating this ie:

If I wanted to duplicate the block of rows several times in a sheet.

Thanks in advance.

[#5]

@RickWilliams 

Sub multiply()

Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim no_of_rows As Integer
Dim start_row As Integer
Dim no_of_multiplications As Integer

Range("F:G").Clear

start_row = Cells(1, 5).Value
no_of_rows = Cells(2, 5).Value
no_of_multiplications = Cells(3, 5).Value

z = 0

For i = 1 To no_of_multiplications
For j = start_row To no_of_rows + start_row - 1

Cells(i + z, 6).Value = Cells(j, 1).Value
Cells(i + z, 7).Value = Cells(j, 2).Value

z = z + 1

Next j
Next i


End Sub

With this code i can enter the start_row, no_of_rows and no_of_multiplications in cells E1, E2 and E3. After clicking the button in cell I2 the block of data is multiplied in columns F and G.

[#6]

Thank you so very much this is amazing!