Master Sheet Row Insertion

[Need Help]

I have a master list that is linked to 5 other Team
worksheets.
Team 1: Rows 1 - 50
Team 2: Rows 51 - 100
Team 3: Rows 101 - 150
Team 4: Rows 151 - 200
Team 5: Rows 201 - 250

Now some of the teams need more record space and I need a
Macro that inserts rows to the end of each team row
allocation as follows:

Check if there are less than 10 empty rows between Teams.
Yes: Add rows to maintain 20 free rows between Teams
No: Do not do anyhting.

My current problem is that my macro adds the rows at the
specific row where the macro was created.

Let us say I created the Macro when Row 35 was empty. Now
whenever I run my current Macro, it Inserts the new rows
into 35 even if I have data in row 34, 35, 36, 37.

I hope this makes sense.

Thanks guys!

 

[Otto Moehrbach]

Excel will need to know how to find the end of each team's data. In other
words, say that Team 1 goes from row 1 to 60 and Team 2 starts at 61, is
there something unique about your data that Excel can use to determine that
row 60 is the last row in the Team 1 data? Or maybe you know that there is
always at least one blank row between teams?
Pretend that you are explaining to a new hire how to do this. What
would you tell him? HTH Otto