VBA to move data to another worksheet based on the value of a cell

[Rdo556]

Hello, I and new to VBA and have tried for a few hours to figure this one out with no success, Please help!

I'm trying to enter a row of data and then generate duplicate rows with the same data based on the Quantity value on "sheet2".

the numbers in "A2" are sequential. so it should grab the next available number and them when running the macro duplicate the line the number of times listed in the qty column.

Example

Serial # PO # IR# Part # Description Heat # Qty Date By
S123456 P654321 I58742 A345678 Bike zGrips 3 =Now() =username


this would output into this on "Sheet2"

Serial # PO # IR# Part # Description Heat # Qty Date By
S123456 P654321 I58742 A345678 Bike zGrips 3 3/13/15 Ryan O
S123457 P654321 I58742 A345678 Bike zGrips 3 3/13/15 Ryan O
S123458 P654321 I58742 A345678 Bike zGrips 3 3/13/15 Ryan O

(Save) then it would be ready for the next entry

Serial # PO # IR# Part # Description Heat # Qty Date By
S123459

all help is much appreciated!!

[Rdo556]

Sample file attached might explain what I'm trying to do better.

[jindon]

My guess...

Sub test()
    Dim myRows As Long
    myRows = Sheets("sheet1").Range("g2").Value
    Sheets("sheet1").Cells(1).CurrentRegion.Rows(2).Copy
    With Sheets("sheet2").Range("a" & Rows.Count).End(xlUp)(2).Resize(myRows)
        .PasteSpecial xlPasteValues
        .Cells(1).AutoFill .Cells(1).Resize(.Rows.Count)
        .Columns("i").Value = Environ("username")
    End With
End Sub

[Rdo556]

Jindon, Thanks! That works well.

1. I want to avoid accidental duplication, is their a way to locate the last serial number in sheet 2 row "A" and automatically start with that number +1?

2. after submit auto save.

3. lastly can sheet 2 highlight the cells entered into sheet 2 and select them and prompt for printing?