# Macro to copy each row multiple times into another sheet



## tonywatsonhelp (Jan 6, 2023)

Hi Everyone,
I'm looking for a macro that can do the following,
I have a sheet called "Product Data"
The headers are in Row 3 so the data starts at row 4
The data range is A4:Z (last row)
Now each row is a different product,
in Column H "Number of Colours" is a number, it can be any number from one up (If cell is blank assume 1)
what I would like is a macro that when run
goes through each Row checks the Number in Column H for that row and makes that many copies into Sheet "Colours" Column A and first empty row,

So If I was writing this in English it would read something like this


For each row in sheet "Product Data" range A4:Zand last row
take the number from column H and copy Row, and paste it that many times in Sheet "Colours" Column A and first empty row,

Please help if you can,
Thanks
Tony


----------



## kvsrinivasamurthy (Jan 6, 2023)

Macro code

```
Sub Macro2()
Dim A
Dim LR As Long, T As Long

LR = Sheets("Product Data").Range("A" & Rows.Count).End(xlUp).Row
A = Sheets("Product Data").Range("A3:Z" & LR)
With Sheets("Colors")
.Range("A1:Z1") = Application.Index(A, 1, 0)
.Range("A1").CurrentRegion.Offset(1, 0).Clear
For T = 2 To UBound(A, 1)
.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(A(T, 8) + 1, 26).Value = Application.Index(A, T, 0)
Next T
End With
End Sub
```


----------



## tonywatsonhelp (Jan 7, 2023)

Thank You Kvsrinivasamurthy this is grerat!


----------

