Inserting Rows - Complicated

[suestew]

I need to insert the number of rows that corresponds with a number in an
existing column. For example: My number at B2 is 3 so I would need to
insert three rows immediately after row 2. My goal would be to have three
rows with the identical information from Row 2 in rows 3,4 and 5. Is this
possible?

 

[StumpedAgain]

Anything's possible.

What I would do:

Sub insertrowhere()

Dim cell As Range
Set cell = Range("B2")

For i = 1 To cell.Value
cell.Offset(1, 0).EntireRow.Insert
cell.EntireRow.Copy Destination:=cell.Offset(1, 0).EntireRow
Next i

End Sub

You can set cell however you want and go from there.
Hope this helps!

 

[suestew]

Thanks! Where do I input the data you've sent me. I'm only slightly
familiar with macros.

I really apprecaite your help. Is there any chance you can call me and walk
me through it?

 

[StumpedAgain]

No problem. It's not too hard to run macros. I would say the problem you'll
probably run into if you're not too familiar with them is in setting "cell"
(especially if you want to do this on more than just "B2"). The following is
a short explaination on what you need to do. If you need more help, I'd be
willing to call you (if you're in the states) this afternoon some time. Good
luck!

Copy the Code
Alt+F11 to start the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select monthly (template) sheet
Alt+F8 to bring up Macros
Highlight the macro name
Run

 

[StumpedAgain]

Oops! Slight change on the instructions (I copied/pasted from a different
post).

Copy the Code
Alt+F11 to start the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
*Select the sheet on which you want to run the macro*
Alt+F8 to bring up Macros
Highlight the macro name
Run

 

[suestew]

Thanks. I'll give it a try. If I have problems, I will let you know.

 

[suestew]

Your plan worked like magic until row 727. Why? I have more rows.

 

[StumpedAgain]

You'll have to be more specific. What's different about row 727 or 728? Are
you moving on to another portion that needs to be copied down?

 

[suestew]

Actually, I was completely wrong. I was running the macro in a spreadsheet I
had tried to fix manually and I had stopped at row 727 out of exhaustion.

I ran the macro in a new sheet and it worked on the very first row, that is
B2 and filled in the subsequent rows as desired. Yet I want it to execute
the macro for all the rows in my spreadsheet. What should I do? I imagine a
slight edit to the macro would be all it takes but I'm not sure.

 

[StumpedAgain]

This should do the trick as long as there aren't any spaces in the B column.
If there are, let me know and I'll figure something else out.

Sub insertrowhere()

Dim cell, cell1 As Range
Set cell = Range("B2")

Do Until cell = ""

Set cell1 = cell.Offset(1, 0)

For i = 1 To cell.Value
cell.Offset(1, 0).EntireRow.Insert
cell.EntireRow.Copy Destination:=cell.Offset(1, 0).EntireRow
Next i

Set cell = cell1

Loop

End Sub

 

[suestew]

Thanks - it worked beautifully.

This should do the trick as long as there aren't any spaces in the B column.
If there are, let me know and I'll figure something else out.

Sub insertrowhere()

Dim cell, cell1 As Range
Set cell = Range("B2")

Do Until cell = ""

Set cell1 = cell.Offset(1, 0)

For i = 1 To cell.Value
cell.Offset(1, 0).EntireRow.Insert
cell.EntireRow.Copy Destination:=cell.Offset(1, 0).EntireRow
Next i

Set cell = cell1

Loop

End Sub