Repeating Rows

[phale]

I am trying to figure out how to automatically repeat the entire contents of
a row a specified number of times instead of having to cut and paste. Does
anyone have any suggestions?

 

[Alan]

Are you inserting the copied row a "Specified number" of times, or adding
them to the end? Is the "Specified number", inputted by the user or is is a
constant number of rows?

Alan

 

[phale]

Not adding --as in calculating a value at the end---just repeating the rows a
certain number of times (this number will vary from workorder to workorder so
this number will be inputted by the user and we will need to be able to
change that value each time).

 

[Alan]

I meant "adding" the copied rows at the end of your data or are inserting
them at the selected or active cell location?

 

[phale]

I "think" the answer to your question is adding them at the selected or
active cell location.
These are for what we call "op" sheets in which we have a specified number
of beams that need cutting, pairing or some other operation done to them. So,
if we need 150 cut to a certain length, we would want to have 150 rows. Then
we might want to turn around--after the beams have been cut--and have them
paired--in which case we would have a different description repated 75 times
since it takes 2 singles to make 75 pairs. The only other information that
are in these op sheets are at the beginning which tells the job number,
customer, date, etc. I'm really ulitimately going to try to put together a
form this information is inputted into. But right now I am just trying to
figure out how to get the rows to repeat.

 

[Alan]

There's probably a simpler way codewise but this will work:

Sub CopyAndInsertRows()
'
Application.ScreenUpdating = False
Dim iCount As Integer
iCount = InputBox("How many rows to insert?")
Do
Selection.EntireRow.Copy
Selection.Insert Shift:=xlDown
iCount = iCount - 1
Loop Until iCount = 0
Application.ScreenUpdating = True
'
End Sub

Alan

 

[phale]

Thank you very much for your prompt answer!
I'm not very VBE literate (trying to learn) so forgive the questions.
Do I need to put the range of cells of the row I want to copy in the
parenthesis in the first row?

Do I replace the "How many rows to insert?" with a number like "150" ?

Do I change the numbers on iCount or Loop?

 

[Alan]

Yes, change:

Selection.EntireRow.Copy

to the range of cells you want to copy. Since your insertion point may
differ each time you run the macro, use something like this:


Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 6)).Copy

which will select the cells, on that row, from A to F, adjust as necessary.

Now, if you do not copy the entire row, then when the insert command runs it
will shift down only the cells below the copied selection. The entire row
will not move down. If that causes you problems, like having data on the same
row but outside your selection, then another approach will have to be taken.

Alan

 

[Alan]

Sorry, I didn't see the other two questions earlier.

Do I replace the "How many rows to insert?" with a number like "150" ?

Yes, you can place any message, that you want to be seen in the InputBox,
between the quotes. The answer below also has a bearing on this.

Do I change the numbers on iCount or Loop?

the iCount value is equal to the value received from the InputBox number
entered by the user. The provided code allows any number of copied rows,
whatever is entered into the InputBox. If that is permit, no change to either
is required. If you have a set number of rows that you want to insert, the
code will need to change to accommodate that. The "150" you are placing in
the quotes above tells me that you might want a set quantity of rows. If that
is the case then let me know.

Alan