Auto Populate Fields

[rminns]

This relates to a thread I started in Excel Worksheet Functions group:

http://www.excelforum.com/showthread.php?t=642149

I need a script that will copy the previous data down to the next row, depending on what the number entered is.

i.e. cell A1 = Dell, B1 = Laptop, C1 = D610, and then D1 = 8 - I need a function that will then copy the information from A1, B1 and C1 down so that I have 8 rows with the same information in (that is the 1 row that I manually entered and then 7 rows with copied data).

In the previous thread we used a formula which worked fine, if the data entered was unique. However the probability is that a user will enter more than one type of data - i.e. the example above would need 8 rows of Dell, Laptop, D610 but the user may then need to enter 10 rows of something completely different in which case the formula breaks down.

Any help would be great - and thanks for NBVC for answers in the previous thread!

[BigBas]

Try using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Qty As Long
    Dim CurrRow As Long
    Dim CurrCol As Long
    
    
    CurrRow = Target.Row
    CurrCol = Target.Column
    
    If CurrCol = 8 And CurrRow >= 14 Then
        Qty = Target.Value
        If Cells(CurrRow, 2) = "" Or Cells(CurrRow, 4) = "" Or Cells(CurrRow, 6) = "" Then
            MsgBox "Please fill in all the fields"
        Else
            Range(Cells(CurrRow, 2), Cells(CurrRow, 7)).Resize(Qty).FillDown
        End If
    End If
End Sub

When you enter a quantity in the quantity column, it will automatically fill down the number in the quantity. Try it out with differing numbers, and report back if you have any questions.

EDIT: I had a code error that I corrected.

[martindwilson]

or try

Sub martin()
Dim y As Integer
y = Range("D1").Value

For X = 1 To y
loopit

Next X
End Sub
Sub loopit()

    Range("A1:C1").Select
    Selection.Copy
    Range("A1").Select
    ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp) _
.Offset(1, 0).Select
    ActiveSheet.Paste
       ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp) _
.Offset(1, 0).Select
    
End Sub

all you have to do is always enter what you want in a1 b1 c1 and number of times in d1 run macro 'martin' (i'd put it on a button labled ''add items" or similar
then this will copy those rows to next free rows that numer of times