Hi,

I am trying to create a macro that copies a row (that contains
formulas) and pastes this row a chosen number of times, using an input
box.

Can anyone help me?

Many thanks in advance!

bozwero:

You don't appear to be able to copy a selection and then run a macro so try
this:

Sub PasteRowANumberOfTimes()
Dim i As Long
Dim rDstStart As Long, rDstNr As Long, rSrc As Long

On Error GoTo error_line

rSrc = InputBox("Row to copy?", "Row Copier...", 1)
rDstStart = InputBox("First row to paste it to?", "Row Paster...", 1)
rDstNr = InputBox("How many times shall I paste it?", "Row Paster...", 1)

Rows(rSrc).Copy

For i = 0 To rDstNr - 1 Step 1
Rows(rDstStart + i).PasteSpecial xlPasteAll,
xlPasteSpecialOperationNone
Next i
Application.CutCopyMode = False
Cells(rDstStart, 1).Select
Exit Sub
error_line:
MsgBox "Error pasting rows, check", vbOKOnly

End Sub



--
Hope this helps
Martin Fishlock


"bozwero" wrote:

Hi,

I am trying to create a macro that copies a row (that contains
formulas) and pastes this row a chosen number of times, using an input
box.

Can anyone help me?

Many thanks in advance!

Hi Martin,

Many thanks for the reply and the suggestion. However, i don't think
this is what i am looking for. Basically i have a spreadsheet template
that has a limited number of rows (50) to enter data into. The user
may need to use more than 50 rows of data, and so i would like the user
to be able to run a macro that asks them how many additional rows they
need, and paste these into the spreadsheet.

I need the macro to copy one row, then ask the user to "Enter number of
rows to add", and then from this, paste the row below the copied row
the chosen number of times.

Does this make more sense?

Thanks again.
Bozwero.


Martin Fishlock wrote:
bozwero:

You don't appear to be able to copy a selection and then run a macro so try
this:

Sub PasteRowANumberOfTimes()
Dim i As Long
Dim rDstStart As Long, rDstNr As Long, rSrc As Long

On Error GoTo error_line

rSrc = InputBox("Row to copy?", "Row Copier...", 1)
rDstStart = InputBox("First row to paste it to?", "Row Paster...", 1)
rDstNr = InputBox("How many times shall I paste it?", "Row Paster...", 1)

Rows(rSrc).Copy

For i = 0 To rDstNr - 1 Step 1
Rows(rDstStart + i).PasteSpecial xlPasteAll,
xlPasteSpecialOperationNone
Next i
Application.CutCopyMode = False
Cells(rDstStart, 1).Select
Exit Sub
error_line:
MsgBox "Error pasting rows, check", vbOKOnly

End Sub



--
Hope this helps
Martin Fishlock


"bozwero" wrote:

Hi,

I am trying to create a macro that copies a row (that contains
formulas) and pastes this row a chosen number of times, using an input
box.

Can anyone help me?

Many thanks in advance!