Insert n-1 rows including formulas

Hello everyone,

I am new to this forum and have a question concerning a macro to which I cannot find a solution.

I am using Excel 2013.


Problem: I have a file with data to various IDs. Each ID can have numerous seasons ranging from 1 to n. The data comes from various other sources using VLOOKUP etc.

What I want to do is to automatically insert n-1 rows beneath each ID copying all data including formulas from the respective row.

The value for n is found in each row, f.ex. ID: 1001 Seasons: 1; ID: 1002 Seasons: 5

The following macro works fine, BUT it inserts n(!) rows and not n-1. Seeing as there already exists a row I need to only enter n-1.


F.ex. ID: 1001 -> enter 1-1=0 rows; ID: 1002 -> enter 5-1=4 rows.

It's a small bug, but it's been driving me mad. Maybe I need a whole new macro, I don't know...

In Row 1 there are the headers and in column C the number of seasons is entered:


Sub EnterRowswithformulas()
Dim RowNo As Long, InsertRows As Long
RowNo = 2
Do
InsertRows = Range("C" & RowNo)
Rows(RowNo + 1 & ":" & RowNo + InsertRows).Insert Shift:=xlDown
Range("A" & RowNo & ":P" & RowNo + InsertRows).FillDown
RowNo = RowNo + InsertRows + 1
Loop Until Range("C" & RowNo) = ""
End Sub


Any help is greatly appreciated!

Cheers!