excel insert rows and paste incremental value

[cstephens]

I need to insert a random number of rows in excel and fill the cell value incrementally by one on each row.
I found the following formula that inserts the blanks rows and copies the value to each row. I want the value to increment by 1 for each new row. It prompts the user for the number of rows to add. But it inserts the row and copies the value down. I need the value to be copied down but add one to the value each time it is copied.
Ex prompt for the number of rows to insert. User enters value of 3. So 3 rows should be inserted. If the value was 111 the next row the cell value would be 112 and the next would be 113, the next 114

Sub copyrows()
numrows = InputBox("Number of rows")
'Rows(ActiveCell.Row).Copy ActiveCell.Resize(numrows)
Rows(ActiveCell.Row).Copy
ActiveCell.Resize(numrows).Insert
Application.CutCopyMode = False
End Sub

Thanks for any help that can be provided.

 

[texasalynn]

Welcome to MrExcel board...

so what cell do you want this value to be inserted?

 

[texasalynn]

here is one way

Sub copyrows()
Dim numrows As Long, Srow As Long, SValue As Long
numrows = InputBox("Number of rows")
RValue = InputBox("What is the next row value to be entered", "Next value", 111)
Srow = ActiveCell.Row
Rows(ActiveCell.Row).Copy
Rows(Srow).Resize(numrows).Insert
Application.CutCopyMode = False
For i = Srow To Srow + numrows
    Cells(i, 1) = RValue
    RValue = RValue + 1
Next i
End Sub

 

[cstephens]

Item number is in row one column A, column B = description, column C = qty, column D will be serial number.
row two column A = a different item, B = desc, C = Qty, D = serial number.
The number of rows inserted will equal to the qty column the serial number will be incremented by one for each new inserted row. It will break on item.

 

[cstephens]

WOW
That works perfectly. GREAT. Thanks so very much for your help.

 

[cstephens]

Ok. Things have changed. Now I need the capability to put an alphabetical character in front of the numbers.
After the prompt for the number of rows. I need another prompt to ask for the prefix. Ex T
then when the value is incremented by one the T would be added or concatenated to the front of the value ex T111 then the next row would be T112 and so on. If an alphabetical character is not entered at the prompt then the value would just be 111 then 112 the way you wrote the formula above.
Help on this would greatly be appreciated.

 

[cstephens]

The macro works fine. How can I change it so the value will be inserted into Column G instead of A?

 

[texasalynn]

change this
Cells(i, 1) = RValue
to the column number or you can use like
Cells(i, "a") = RValue

 

[cstephens]

That took care of it.
Thanks again.