Auto fill text using formula or code

**nicmarty** · 10-28-2010, 12:29 PM

I am looking for a formula or code that will take information from Sheet1 and on Sheet2 it will copy down each column and duplicate each row based on the value in Column I. So for instance, Row 1 will duplicate 5 times before moving on to the next row which will only be listed once on sheet 2. I have filled in on Sheet 2 what I would like it to look like. Thanks in advance

**StephenR** · 10-28-2010, 12:35 PM

Weirdly this is almost exactly the same question as epi's, immediately below yours.

**nicmarty** · 10-28-2010, 12:54 PM

Wow, that is weird!

**nicmarty** · 10-28-2010, 01:14 PM

Can this be done from by pulling from sheet1 to sheet2 like I'd like to do? I have formulas in sheet1 which pulls all that info to begin with, I don't want to affect sheet1 at all.

**StephenR** · 10-28-2010, 02:15 PM

Sorry, that wasn't a very helpful response from me. Easily done with code, I'll look tomorrow if no-one else has in the meantime.

**StephenR** · 10-29-2010, 05:56 AM

If you clean up the random entries on sheet1 around row 2000, this should work:

Sub x()

Dim v, i As Long, j As Long, k As Long

Application.ScreenUpdating = False

v = Sheet1.Range("A2", Sheet1.Range("A" & Rows.Count).End(xlUp)).Resize(, 9).Value

Sheet2.UsedRange.Offset(1).ClearContents

For i = LBound(v, 1) To UBound(v, 1)
    With Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
        For k = 1 To 9
            .Offset(, k - 1).Resize(v(i, 9)).Value = v(i, k)
        Next k
    End With
Next i

Application.ScreenUpdating = True

End Sub