Copy rows and insert (x) number of times

[eric.d.soelberg]

I am trying to copy two areas of my spreadsheet and insert those two
areas "X" number of times. I am novice with VBA and tried to take two
strings of code I found on the internet and splice them together, but
to no avail. Here's what I have right now, which is popping up a
prompt asking number of rows, and then it proceeds to copy the two
selections I want and insert them, just like I want it to for ONE
insert. Anyone know how I can get it to insert "X" number of rows
(the number entered into the prompt)?

Here's my current code:

Sub InsertRow()
Dim Rng
Rng = InputBox("How many additional states?")
Range("42:52").Copy
Rows("53").Select
Selection.EntireRow.Insert
Rows("30").Copy
Rows("31").Select
Selection.EntireRow.Insert
Application.CutCopyMode = False
End Sub


Any help is much appreciated!!

 

[JLGWhiz]

There is nothing in the posting to indicate the criteria for selecting either
the rows to copy or the row locations to insert. In fact, it looks as if
those selections are arbitrary. If you can explain what determines which row
to copy and how to determine where to insert them, maybe someone can provide
some code. I can see no connection between the inputbox value and any of the
ranges copied or inserted.

 

[eric.d.soelberg]

There is nothing in the posting to indicate the criteria for selecting either
the rows to copy or the row locations to insert.  In fact, it looks as if
those selections are arbitrary.  If you can explain what determines which row
to copy and how to determine where to insert them, maybe someone can provide
some code.  I can see no connection between the inputbox value and any of the
ranges copied or inserted.

Thanks for the reply. I'll try and clarify better.

I have a template in excel, which can remain static, except for data
in row 30 and rows 42-52, which I am trying to be able to copy and
insert (X) number of times, depending on my needs. For example, I am
trying to be able to get the prompt to pop up when I click a button
asking me how many rows I need, and then if I type "5", five rows of
row 30 will be copied and insterted below row 30 and five copies of
rows 42-52 will be copied below those rows (starting on row 53).

I got this code online:
Sub InsertRow()
Dim Rng
Rng = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rng - 1, 0)).Select
Selection.EntireRow.Insert
End Sub

This code got the prompt to pop up and put (X) numbers of rows from
the active cell - but no copying was done and I want it to be able to
just always do (X) versions of row 30 below row 30 and (X) versions of
rows 42-52 below those rows.

So by recording my own macro and then trying to use the one pasted
above, I came up with the code originally posted. I know I need to
use the rng code still, probably combined with "offset", but when I
tried to replace the term "ActiveCell" with the rows I want copied, it
gave me errors.

Hopefully this helps, and THANK YOU to anyone who can help correct my
code.

 

[JLGWhiz]

Try this on a test page before installing it for permanent use.

The code is to be pasted into the standard code module1. Press Alt + F11 tp
access tje VBE.

Sub multicopy()
Dim x As Long, y As Long
numb = InputBox("Enter number of times to insert.", "Iteration")
x = CLng(numb)
Rows("42:52").Copy
y = Rows("42:52").Rows.Count * x
Range("A53").Resize(y, 1).Insert
Rows(30).Copy
Range("A31").Resize(x, 1).Insert
Application.CutCopyMode = False
End Sub

 

[Eric S]

Thanks JLGWhiz - worked perfectly. If you don't mind my asking, how
did you get so good at this? OJT? Reading books? I'm realizing the
potential of VBA to do some amazing things and want to get better...

Thanks